Checking if point lies with GeoJSON polygon in PostgreSQL

Question

I have already parsed my GeoJSON in PostgreSQL and my table looks exactly like the answer to Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS?

Now I want to query PostgreSQL with lat/lon and check if a point lies within a polygon or not and if the point lies within a polygon then print the city name. So far I have tried this :

SELECT *
FROM locations
WHERE ST_DWithin(geom, 'POINT(71.013 11)', 10.0);

But this is obviously wrong because I don't want to specify a distance within which I want to check. I just want if a point lies in a certain polygon in database then print the row

aside from st_intersects you can use ST_DWithin just specify the distance to 0.001 or something minute...sometimes you can get more accurate results than an st_intersects... — ziggy, Mar 08 '18 at 20:31

score 4 · Accepted Answer · edited Nov 16 '22 at 07:05

Have a good read on spatial relation functions in PostGIS, and get aquainted with the detailed usage parameters of those functions via the PostGIS docs. I dare say this is the most frequently used functionality in spatial database analysis; as a beginner (I hope I assume correctly) and for simple tasks, knowing about those alone will drastically improve your work with PostGIS.

In this basic case, ST_Intesects will most likely be the one you are looking for:

SELECT <city_name_column>
FROM locations
WHERE ST_Intersects(geom, 'POINT(71.013 11)')

This should return all city names in <city_name_column> that spatially intersect with the given point.

However, this already implies knowledge about a few other things, e.g. the coordinate referrence system you are using. For all relation functions to make sense, the geometries to be compared must be referred in the same CSR. If that is not the case, you will need to transform one of those CRS to match the other.

Sidenote: while the use of ST_Intersects is rather straight forward, other spatial relation functions do have specific behaviours and need to be used with care to get proper results (e.g. the order in which the geometries are given to the function, etc.). ST_Within(A, B) for example returns true if geometry A is completely inside geometry B (note the order), which, unlike ST_Intersects, does not consider a point if it lies on the boundary of a polygon.

Checking if point lies with GeoJSON polygon in PostgreSQL

1 Answers1