CODEWITHSUNDEEP
phpmysqlpolygon
shasi kanth
shasi kanth

Reputation: 7094

Get polygon points mysql

i have created a table in mysql to store polygon data:

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))')
);

Now how can i get back the vertices (points) of this polygon in mysql? Why i am asking means, later i want to find whether a point is inside a polygon. And to achieve this, i hope i need the polygon vertices.

Upvotes: 10

Views: 16688

Answers (3)

conmak
conmak

Reputation: 1470

To answer your question, a great option would be to output the GeoJSON format. See more here: (https://dev.mysql.com/doc/refman/5.7/en/spatial-geojson-functions.html)

select ST_AsGeoJSON(g) from geom;

To help with your actual problem of selecting points in the bounding polygon: @jcorry has a great solution with ST_Contains

To further expand this out to select all points from a coordinates table that the Polygon encapsulates, you can do something like: 

select
ST_X(point) as x,
ST_Y(point) as y   
from coordinates
where ST_Contains(
    (select g from geom limit 1), 
    point
)

Upvotes: 5

John Corry
John Corry

Reputation: 1577

If you want to find whether a point is in a polygon, you don't need to derive the individual vertices to do that. There's a function in MySQL (5.6+) for this:

SELECT ST_Contains(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))'), PointFromText("POINT(10 42)")
);

Upvotes: 4

Wrikken
Wrikken

Reputation: 70460

If you want the WKT back: SELECT AsText(g) FROM geom;

Upvotes: 19

Related Questions