2

I have two tables. One has points in EPSG:4326 and the other one has polygons also in EPSG:4326. I loaded these two as layers in geoserver and show them using openlayers (as shown in image)

Now I want to write a query in postgis in which I find the intersection area of each green point (buffered by 200 meters) with the polygons.

I have written this query:

SELECT c.name, d.name FROM points c, polygons d WHERE ST_INTERSECTS(d.geom, ST_BUFFER(c.geom,500))

But I get no results. If I increase the buffer size to 5000000 then I get results.

  1. So the question is if there is a better way to do this without using a Buffer.
  2. If there is projection issue in which I am missing.

EDIT

I followed the advice of @HimBromBeere and transformed my geometries to EPSG: 2100 (which is in metres). The problem still remains as executing this query I get as a result 0, which doesnt make any sense.

SELECT ST_AREA(
      ST_INTERSECTION(ST_Buffer
      (ST_Transform(ST_SetSRID(c.geom, 4326),2100),200),
      ST_Transform(ST_SetSRID(d.geom, 4326),2100))) 
      FROM categoriesdata c, dimoi d 

enter image description here

  • What happens when you ommit the ST_SetSRID-part? – HimBromBeere Dec 1 '15 at 13:03
  • @HimBromBeere it also returns the same. A column with 0 values. – user1919 Dec 1 '15 at 13:20
3

ST_Buffer will consider your spatial reference. So as you have geographic coordinates you have to provide your buffer-size in degrees as well.

Alternativly you may transform your geometries into a metric spatial-refrence first as proposed here:

SELECT ST_Buffer(
    ST_Transform(
        ST_SetSRID(ST_MakePoint(-71.063526, 42.35785), 4326),
        myNewReferenceSystem), 
    200); // buffer by 200m
  • Please see my edit. – user1919 Dec 1 '15 at 12:55

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.