1

I'm trying to write a query to find if any features in a layer intersect with a certain polygon, but it is always returning 0 rows. I've tried messing with SRIDs, different geom types, sizes, shapes, etc. and have never had it return a single result, making me think there is just a basic misunderstanding I'm having.

The geometry data in the database is stored under the "geometry" column in the WKT format with the 3857 SRID. All I want to is to get returned all the rows whose geometry touches the polygon at all.

The query I wrote looks like this:

SELECT *
FROM tableOfFeatures
WHERE ST_Intersects(ST_GeomFromText('SRID=3857;POLYGON((-118.09636448889168 83.80774305261292, -103.722647355961 -83.72028886793554, -13.87670908426287 -83.7764092545634, -38.85866039455652 84.01409674357751, -118.09636448889168 83.80774305261292))'), geometry);

Am I missing some kind of conversion or cast?

  • 2
    Your polygon seems to be in SRID 4326 (geographic coordinates), not 3857. – Mesa Dec 17 '18 at 22:22
  • @Mesa ah yeah, but when I change it to 'SRID=4326' it still doesn't return anything. You can see that polygon takes up half the map, and the layer has points everywhere. Maybe I don't understand exactly how the projections convert. – TJ_ Dec 17 '18 at 22:26
2

Turns out I needed to Transform from 4326 to 3857 projection.

Thanks to @Mesa for getting me going in the right direction.

Here's what ended up working

SELECT *
FROM tableOfFeatures
WHERE st_intersects(geometry,
                ST_Transform(
                  ST_GeomFromText('SRID=4326;POLYGON((-118.09636448889168 83.80774305261292, -103.722647355961 -83.72028886793554, -13.87670908426287 -83.7764092545634, -38.85866039455652 84.01409674357751, -118.09636448889168 83.80774305261292))')
                  , 3857)
      )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.