Hi, i have inserted a polygon with points into a mysql database. Now
how can i retrieve back the points associated with this polygon?
(hopefully in WKT format).

What i want to do is to check whether a point lies inside polygon, and
for doing this, i hope i need the vertices of the polygon.

Please help.

shashi kanth <dskanth.99 (AT) gmail (DOT) com> writes:

How did you insert them?

sherm--

--
Sherm Pendley
<http://camelbones.sourceforge.net>
Cocoa Developer

Hi, first i created the table like this:

CREATE TABLE geom (g GEOMETRY);

And i inserted a polygon into it, using the following syntax:

INSERT INTO geom (g)
VALUES(PolygonFromText('POLYGON((
9.190586853 45.464518970,
9.190602686 45.463993916,
9.191572471 45.464001929,
9.191613325 45.463884676,
9.192136130 45.463880767,
9.192111509 45.464095594,
9.192427961 45.464117804,
9.192417811 45.464112862,
9.192509035 45.464225851,
9.192493139 45.464371079,
9.192448471 45.464439002,
9.192387444 45.464477861,
9.192051402 45.464483037,
9.192012814 45.464643592,
9.191640825 45.464647090,
9.191622331 45.464506215,
9.190586853 45.464518970))')
);



On Feb 24, 7:37*pm, Sherm Pendley <sherm.pend... (AT) gmail (DOT) com> wrote:

shashi kanth <dskanth.99 (AT) gmail (DOT) com> writes:

Ah, I see the problem now. A simple select will get you back the whole
polygon string in WKT format:

SELECT AsText(g) FROM geom;

But, I don't see any easy way to parse that string into its individual
point components.

sherm--

--
Sherm Pendley
<http://camelbones.sourceforge.net>
Cocoa Developer

Thanks, but i want to know the points that constituted the polygon...

Well, i could get a string format of the polygon vertices using:

SELECT cast(AsText(g) as char) AS points FROM geom

On 25-02-11 11:14, shashi kanth wrote:
This gives same result, because result of AsText() function obviously is
Text....

SELECT AsText(g) AS points FROM geom;

--
Luuk

On 24-02-11 15:26, shashi kanth wrote:
i googled a bit, and in the docs
(http://dev.mysql.com/doc/refman/5.0/...xtensions.html) at the
bottom of the page is a link to
If you have questions or concerns about the use of the spatial
extensions to MySQL, you can discuss them in the GIS forum:
http://forums.mysql.com/list.php?23

On that forum is a question about "Point in Polygon Problems"
http://forums.mysql.com/read.php?23,...562#msg-398562


SELECT AsText(g) FROM geom WHERE MBRWithin(PointFromText('POINT(9.1935
45.46411 )'),g);
returns nothing

SELECT AsText(g) FROM geom WHERE MBRWithin(PointFromText('POINT(9.1915
45.46411 )'),g);
returns the polygon....

--
Luuk

On 2011-02-24 15:26, shashi kanth wrote:
You will probably need some kind of procedural construction. A sketch
(<dislaimer>: I never worked with gis)

drop temporary table points;
create temporary table points (
n int not null,
p varchar(50) not null
);


delimiter @
drop procedure extract_points @
create procedure extract_points ()
begin
declare curr int default 1;
declare endp int;

select NumPoints(ExteriorRing(g)) into endp from geom;
while (curr <= endp) do
insert into points (n, p)
select curr, AsText(PointN(ExteriorRing(g), curr)) from geom;
set curr = curr + 1;
end while;

end;
@
delimiter ;

call extract_points;
select * from points;

mysql> select * from points;
+----+---------------------------------+
17 rows in set (0.00 sec)


I googled a bit and found a number of algorithms that determines whether
a point belongs to the interior of a polygon. I did not investigate them
very much, but at a glance it looked possible to implement in a
procedure (I don't think sql is the best language for this, but since
you probably will end up with a procedure anyhow, you might concider it)


/Lennart