CODEWITHSUNDEEP
postgresqlpostgis
David Murdoch
David Murdoch

Reputation: 89322

Using ST_Intersects with ST_MakePoint with no SET_SRID

I'm using WHERE ST_Intersects(ST_SetSRID(ST_MakePoint($1, $2)::geography, 4326), geog) to find a point within a geography field (named geog in the example query).

For reasons I can't quite figure out*, ST_SetSRID sometimes causes issues, removing it from the query makes these issues go away. I'd like to remove ST_SetSRID from the query but can't find anywhere that explains what SRID ST_Intersects will use.

geog has an SRID of 4326. Will ST_Intersects just use that or is going to assume no coordinate system and give me results that differ than when using ST_SetSRID?

* In case you are curious the issue has something to do with prepared transactions, nodejs, and the minimum connection pool. For 1 minimum connections in the pool, after 4-6 queries the next query will take 15-30 seconds (which usually takes about 100ms). For 2 min connections it takes about 8-10 queries before issues occur, for 5 min, about 25 queries (and so on). I feel like I'm taking Crazy Pills.

Upvotes: 1

Views: 499

Answers (1)

Mike T
Mike T

Reputation: 43622

ST_SetSRID returns a geometry, not a geography. You generally don't need to set the SRID for geography, since it assumes a default of 4326, so I suggest not using it (unless you have a different ellipsoid or something). (But if you are working with geometry, ST_SRID is mandatory).

Furthermore, ST_Intersects implicitly operates on either geometry or geography types. Depending if you used ST_SetSRID or not, it will pick either:

  • ST_Intersects(geometry, geometry); or
  • ST_Intersects(geography, geography)

You can explicitly choose the one of the operators by casting each parameter:

ST_Intersects(ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography, geog::geography)

(note I've moved the first ::geography to outside ST_SetSRID, so it sets an SRID then casts it as a geography). Or equivalently:

ST_Intersects(ST_MakePoint($1, $2)::geography, geog::geography)

As for the actual performance of the two intersects spatial operators, this depends if you have an index on either geometry or geography types for geog.

Upvotes: 4

Related Questions