PostGIS query to Select all Polygons from Polygon table having an intersecting geometry with one or more point in passed list of GeoCoordinates

Question

I have a polygons table in Postgres (using PostGIS extension) named polygon having two fields (geom, id).

If I want to query the id of the polygon which intersects with the geometry of input geo-coordinate then I can do it with the below query.

SELECT id, geom 
FROM polygon 
WHERE ST_Intersects(polygon.%s, ST_GeometryFromText(POINT(latitude logitude), 4326));

But now I have a use case where I am getting a lot of geo-coordinates in request(~60k), now I am breaking this into lists of 1k Geo-coordinate each and querying the id of the polygon intersecting with each geo-coordinate.

I am struggling with how to write. a query for this, or if anyone has a better solution for this please suggest.

Jim Jones Jim Jones · Accepted Answer · 2020-07-24T09:56:16

Keep in mind that the right order of coordinate pairs is lon, lat, so creating a point with lat, lon in your query will return wrong results. Your query also misses the single quotes ' around the WKT coordinate, e.g. 'POINT(1 2)'.

That all being said, you could simply paginate your result sets using ORDER BY, LIMIT and OFFSET, e.g.

Getting the first 1000 records

SELECT id, geom FROM polygon
WHERE ST_Intersects(geom, 'SRID=4326;POINT(1 2)')
ORDER BY id
LIMIT 1000 OFFSET 0;

By changing the OFFSET value you are able to retrieve the next pages.

LIMIT 1000 OFFSET 1000;

and so on ..

LIMIT 1000 OFFSET 2000;

EDIT: One way to apply this query using multiple input points is to use a CTE / subquery (see comments), e.g.

WITH j(g) AS (
  VALUES 
    ('SRID=4326;POINT(1 1)'),
    ('SRID=4326;POINT(1 2)') 
    -- ... add as many geometries as you want
)
SELECT id, geom FROM polygon, j
WHERE ST_Intersects(geom, g::geometry)

PostGIS query to Select all Polygons from Polygon table having an intersecting geometry with one or more point in passed list of GeoCoordinates

1 Answers