-1

I want to identify given point is in which polygon. i am using mysql database. Point is from text and multipolygon is in Shape column as geometry.i am using following query.

SELECT ap.ac_name
FROM andrapradesh ap
WHERE ST_Contains(PointFromText('POINT(16.504181 78.161779)'), ap.SHAPE)=1;
munsifali
  • 1,732
  • 2
  • 24
  • 43
Sravan Kumar
  • 9
  • 3

3 Answers3

0

If you want use ST_Contains() You should use 

SELECT ap.ac_name 
FROM andrapradesh ap
WHERE ST_Contains(ap.SHAPE, PointFromText('POINT(16.504181 78.161779)');

ST_Contains(g1, g2)

Returns 1 or 0 to indicate whether g1 completely contains g2. This tests the opposite relationship as ST_Within().

If you want use the contained element as first param you need ST_Within 

SELECT ap.ac_name 
FROM andrapradesh ap 
WHERE ST_Within(PointFromText('POINT(16.504181 78.161779)'),ap.SHAPE);

ST_Within(g1, g2)

Returns 1 or 0 to indicate whether g1 is spatially within g2. This tests the opposite relationship as ST_Contains().

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • You comment is not clear . .. my answer at your question about ST_Contains work or not .. ? – ScaisEdge Apr 02 '19 at 09:15
  • my question is given point is is which area polygon. and ST_Within is not working – Sravan Kumar Apr 02 '19 at 09:18
  • You question .. use ST_Contains and not ST_within .. and you are using ST_Contain in wrong way .. first argument is the container second is the contained .. and if you want the firts argument the contained the use ST_within and not ST_Contains as in you question code .. answer updated – ScaisEdge Apr 02 '19 at 09:24
  • i used the this now. but i am getting empty results. and shape is not polygon in table. it is geometry type.. – Sravan Kumar Apr 02 '19 at 09:28
  • then you should check the real coord for the polygon and the point .. are you sure the polygons coords are in a valid Coordinate System Reference (SR) coherent with SR for the point??? .. try build a polygon using text too with valid four point – ScaisEdge Apr 02 '19 at 09:30
  • yah i have given 4326 as projection. database aswell same projrction – Sravan Kumar Apr 02 '19 at 09:31
  • try build the polygon as text ( a simple square) .. and check .. .. remeber that also the POINT must be in Coherent SR .. – ScaisEdge Apr 02 '19 at 09:33
0

Yes I Got Output. What id did is i chat lat and long.

SELECT ap.ac_name 
FROM andrapradesh ap 
WHERE ST_Within(PointFromText('POINT(78.161779 16.504181)'),ap.SHAPE);
Sravan Kumar
  • 9
  • 3
0
ST_Intersection(g1, g2)

Returns a geometry that represents the point set intersection of the geometry values g1 and g2. If any argument is NULL, the return value is NULL.

mysql> SET @g1 = ST_GeomFromText('LineString(1 1, 3 3)');
mysql> SET @g2 = ST_GeomFromText('LineString(1 3, 3 1)');
mysql> SELECT ST_AsText(ST_Intersection(@g1, @g2));

+--------------------------------------+
| ST_AsText(ST_Intersection(@g1, @g2)) |
+--------------------------------------+
| POINT(2 2)                           |
+--------------------------------------+
tayfun Kılıç
  • 2,042
  • 1
  • 14
  • 11
  • Please explain your answer in detail – TheParam Apr 05 '19 at 07:16
  • While what you have written may answer the question, however it does seem a little lacking in [explanation](https://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) and may illicit confusion to other users. Can you please expand upon your answer so that it is clearer and more accessible? This will make for better answers and help future users understand how the problem was solved. – Andrew Apr 05 '19 at 07:45
  • Okey andrew Sorry – tayfun Kılıç Apr 05 '19 at 07:49