2

I'm trying to find out all the circles stored in the database(The circles are present all over the world) that intersect or overlap a given circle. I've stored the circles like this:

CREATE TABLE circles(gid serial PRIMARY KEY, name varchar, code integer, the_geog geography(POLYGON,4326) );

INSERT INTO circles (gid, name, code, the_geog) VALUES (1,"hello", 11,geometry(ST_Buffer(geography(ST_GeomFromText('POINT(48.732084 -3.459144)')), 1000)));

Now when I'm trying to find out all the circles that intersect a given circle, I'm getting this error:

ERROR:  Only lon/lat coordinate systems are supported in geography.

I'm using this query to get the data:

select * from circles where st_intersects (circles.the_geog,ST_Buffer(ST_Transform(ST_GeomFromText('POINT(-105.04428 39.74779)', 4326), 2877), 1500));

Can anyone tell me what I'm doing wrong ?

1

I think the problem is that your st_intersects mixes a geography and a geometry; I guess that Postgres is trying to cast your geometry to geography, and that fails because srid 2877 is, as your error messages says, not a lat/lon coordinate system.

Easiest way is to do everything using geographies and st_geogfromtext (the st_buffer will also draw the circle in metres - if you use geometry it will use the coordinate system units).

select * from circles where 
st_intersects (
   circles.the_geog,
   ST_Buffer(       
              ST_GeogFromText('SRID=4326;POINT(-105.04428 39.74779)')
              , 1500
   )
);

P.S. You can improve your insert query by not using the transform:

INSERT INTO circles (gid, name, code, the_geog) 
VALUES (
        1
        ,'hello' -- need single quotes here
        , 11
        ,ST_Buffer(ST_GeogFromText('SRID=4326;POINT(48.732084 -3.459144)'), 1000)
        );
1

Try to use ST_GeogFromText instead of ST_GeomFromText

select * from circles where st_intersects (towers.the_geog,ST_Buffer(ST_Transform(ST_GeogFromText('POINT(-105.04428 39.74779)', 4326), 2877), 1500));
  • Getting this error: ERROR: function st_geogfromtext(unknown, integer) does not exist – Rahul Jun 19 '16 at 19:52
  • Even if you fix the format of the ewkt (should be 'POINT(-105.04428 39.74779)' without the srid 4326), the st_transform fails because that only works on geometries. – mlinth Jun 20 '16 at 9:15

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.