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

Question

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 ?

Answer 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)
        );

Answer 2

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));