0

I am using postGreSQL (9.3) and postGIS (2.1). I have a table containing names and circles (point + radius).

Given a name and a circle (point + radius), I would like to find all the database lines that contains (circles that intersect my circle) AND (share the same name). Is it possible using postGIS ?

Here is what I have so far, taken from other questions on this site. Please note that I am just beginning to learn postGreSQL :

--create table

CREATE TABLE circles (
    city VARCHAR(50),
    geog geography
);

-- insert sample values

INSERT INTO circles (city, geog)
VALUES ('Lannion', geometry(ST_Buffer(geography(ST_GeomFromText('POINT(48.732084 -3.459144)')), 1000)));

INSERT INTO circles (city, geog)
VALUES ('Lannion', geometry(ST_Buffer(geography(ST_GeomFromText('POINT(48.732084 -3.459144)')), 2000)));

INSERT INTO circles (city, geog)
VALUES ('Lannion', geometry(ST_Buffer(geography(ST_GeomFromText('POINT(48.732084 -3.459144)')), 3000)));

INSERT INTO circles (city, geog)
VALUES ('Lannion', geometry(ST_Buffer(geography(ST_GeomFromText('POINT(48.732084 -3.459144)')), 4000)));

INSERT INTO circles (city, geog)
VALUES ('anotherName', geometry(ST_Buffer(geography(ST_GeomFromText('POINT(48.732084 -3.459144)')), 1000))); -- not selected because the name is different

-- search (need help for this one) 
SELECT ST_Buffer(geometry(ST_MakePoint(48.732084, -3.459144)), 2500) FROM circles;

I can't make sense of the output of the SELECT query so far. Also, it doesn't have any constraint on the city name. I would like to have as output the first two inserts in this case.

Improve this question
8
  • Could you explain in words what your query should look like. ST_Buffer simply expands an existing geometry, it does not find intersections. I think you are looking for ST_Intersects in conjunction with some use of city in the WHERE clause.
    – John Powell
    Apr 8, 2015 at 21:21
  • @JohnBarça Yes ST_Intersects is probably what I want. The serach query is more of an attempt, this is why I gave the example result it should return. I should maybe remove my attempt to make the question more clear ?
    – nha
    Apr 8, 2015 at 21:53
  • I believe what you're after is ST_DWithin() -- it's almost always what should be used when people first think of using st_buffer(). Check out the many questions here regarding ST_DWithin(). E.G. gis.stackexchange.com/q/77688/3195; gis.stackexchange.com/q/125187/3195; gis.stackexchange.com/q/63981/3195; gis.stackexchange.com/q/32711/3195
    – Martin F
    Apr 9, 2015 at 3:49
  • You mention circles. Are you sure you have circular entities, or are you just doing distance based searches?
    – Martin F
    Apr 9, 2015 at 3:53
  • Hello @MartinF you are right I am doing distance based searches. Should I use ST_DWithin() ?
    – nha
    Apr 9, 2015 at 7:24

1 Answer 1

Reset to default
0

Judging from comments, you are looking for a simple geometry(point) intersects all the geometry on a table right?

What you can use it should be something like this; 

select * from circles 

where st_intersects (circles.geog,(ST_PointFromText('POINT (48.732084 -3.459144)',4326)))

Mind the SRID mixing problem. Because i gave 4326 to the selectiong geometry.

Improve this answer
4
  • Thank you I'll try that when I get home :) Can you expand a bit on the SRID thing (or just give a pointer to a good ressource) ?
    – nha
    Apr 9, 2015 at 12:46
  • No, OP doesn't actually have circles, just points and nearby lines. But, yes, do pay attention to CRS (coordinate reference system), referred to in PostGIS via SRID (spatial reference system ID).
    – Martin F
    Apr 9, 2015 at 15:20
  • Could you expain to me more specific because my English is not that good. Maybe I misunderstood or something. Maybe a explaining with drawing object will be awesome. So that i'll try help. What i am saying that the insert sql on top is making those points into a polygon. Actually the circles tables is polygon. Am i right?
    – lokmancetin
    Apr 10, 2015 at 8:24
  • I did change the SRID to 900913 to avoid a ERROR: Operation on mixed SRID geometries. But it always returns all points, and doesn't seem to take into account the distance between circles. It doesn't work in my example for instance.
    – nha
    Apr 13, 2015 at 19:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.