1

I am trying to do a simple query in PostGIS to get the difference of a line from the buffer of another line. The lines are from latLng coordinates but I want the buffer to be in kilometers.

In the image, I show what I am trying to get with ST_Difference.

enter image description here

The following query will not give me the correct answer

SELECT ST_Difference(ST_LineFromText('LINESTRING(-3.444 52.468, -1.165 52.468)'), ST_Buffer(ST_LineFromText('LINESTRING(-1.725 52.330, 0.604 52.330)'), 25000))

and I did not expect it to work, cause I somehow have to define a coordinate system and what this "25000" at the end, means. I tried a lot with ST_Transform and ST_SetSRID but nothing worked. Also, I would like a solution without altering the table or something.. Just a query that would work.

4

ST_BUFFER() works with map units, so your query makes a buffer of 25,000 degrees.

To be able to create a buffer in meters, you would either need to switch to a coordinate system whose unit is in meters - and that is suitable to your area to avoid excessive distortion, or to use the geography type. Be aware though that lines are conceived as great circles, not as straight line.

You query would need to set the current coordinate system (presumably 4326), cast to geography, do the buffering, then cast back to geometry and at last compute the difference.

Keeping the same 25 km buffer, the query becomes:

SELECT ST_Difference(
          ST_LineFromText('LINESTRING(-3.444 52.468, -1.165 52.468)',4326),
          ST_Buffer(
            ST_LineFromText('LINESTRING(-1.725 52.330, 0.604 52.330)',4326)::geography, 
            25000)::geometry);

which creates an output of

LINESTRING(-3.444 52.468,-2.01273839187683 52.468)
2

Similar to the Answer by @JGH already, but I've already started working it out so I'll post anyway.

This method Transforms the lines from a global 4326 to a local coordinate system 32630 (which is suited for the area shown) then buffers this to allow use of meters as a distance.

Outputs are put in a new table called difference_line :

CREATE TABLE difference_line AS
SELECT 
1 id, (ST_Difference(f_line.geom, ST_Buffer(s_line.geom, 25000))) geom
FROM 
(SELECT ST_TRANSFORM(ST_GeomFromText('LINESTRING(-3.444 52.468, -1.165 52.468)', 4326), 32630) geom) as f_line,
(SELECT ST_TRANSFORM(ST_GeomFromText('LINESTRING(-1.725 52.330, 0.604 52.330)', 4326), 32630) geom) as s_line;

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.