1

I have a very slow query and isolated the problem to casting my geometry type to geography. Here is my query, which basically calculates a bounding box of 15 meters (hence the need for casting to geography) around a LineString:

select st_buffer(cast(st_setsrid(lines, 4326) as geography), 15) as geometry from linesTable where id = 1

The table linesTable contains the geometries in the column lines. The cast is fine with relatively small LineStrings, but if they contain something like 500 points or more, then it takes half a second for the casting alone. The rest of the query is quick.

I need to speed this up by a factor of five or so. My concrete and to the point question is:

How can I speed up casting a complex linestring from geometry to geography? In the above query this would be the innermost part only:

cast(st_setsrid(lines, 4326) as geography

Note that st_setsrid is added for clarification only. If I drop it, then the query does not become any faster.

closed as too broad by Vince, ahmadhanb, Jochen Schwarze, Ian Turton Mar 2 at 16:42

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Welcome to GIS SE. As a new user, please take the Tour, which emphasizes the importance of asking One question per Question. You have a number of operations that, if they need to be done every time, could be done once. The usual reason not to do this is because of data volume and frequency of update. Please edit the question to address the larger picture (and include what "too long" means). Note that this question could be closed as too broad or opinion-based, so you may need to thread the needle in your phrasing. – Vince Mar 2 at 14:09
  • 1
    Maybe the problem is not the cast to geography, but the ST_Buffer of the geography data type. You can try projecting the linestring to a proper SRID, make the buffer there and then reproject again to the original. Also, you can speed up the ST_Buffer with a third argument: less than 8 segments or adding another endcap and join parameter, like 'endcap=square join=mitre'. Finally, if you are buffering for a radius search, use ST_DWhitin instead. postgis.net/docs/ST_Buffer.html – Gabriel De Luca Mar 5 at 6:35
  • Using ST_DWithin solved the problem, many thanks for that. I saw this recommendation before in the docs, but I thought "radius search" would apply to searching in a circle only, not in an irregular bounding box. I now realised that ST_DWithin simply determines distances, which is exactly what I need. I would like to accept your answer, but can't for some reason. – ulim Mar 5 at 16:29

Browse other questions tagged or ask your own question.