Thread: Points, Circles, Indexes, and query-by-Radius?
I'm developing a GIS system on PostgreSQL. I need to store a table of points (latitude and longitude), and do queries such as "return all points enclosed with the given circle ((X,Y),R)". The "point" data type looks convenient, however, it doesn't appear to be indexable. Can anyone tell me what the preferred, recommended strategy for this design pattern is, with respect to PostgreSQL? One which makes good use of indices, if possible (realizing that I personally don't know a good way to index for a radius search). For extra credit, it would be nice if I could store circles instead of points, and issue queries like the following: "given a circle ((X,Y),R), return all circles which share some common area" Opinions would be appreciated -- I looked at the online docs, but could not find any discussion about optimizing this kind of data schema and query requirement. Thank you, Bryan
Bryan, The default geometric types in PostgreSQL really aren't particularly well suited for GIS (2-D only, no support for polygons w/ holes, not particularly indexable, etc) which is why we've started the PostGIS project. Head to http://postgis.refractions.net for more info, and code you can download to add better GIS objects (with indexing and conforming to OpenGIS specs) to PostgreSQL. The package compiles very straightforwardly under contrib, and is (hopefully) well enough documented to get a start with. Paul Bryan Field-Elliot wrote: > > I'm developing a GIS system on PostgreSQL. I need to store a table of > points (latitude and longitude), and do queries such as "return all > points enclosed with the given circle ((X,Y),R)". Straightforward in PostGIS. > The "point" data type looks convenient, however, it doesn't appear to be > indexable. > > Can anyone tell me what the preferred, recommended strategy for this > design pattern is, with respect to PostgreSQL? One which makes good use > of indices, if possible (realizing that I personally don't know a good > way to index for a radius search). > > For extra credit, it would be nice if I could store circles instead of > points, and issue queries like the following: > > "given a circle ((X,Y),R), return all circles which share some common area" More tricky. We don't do circles (not an OpenGIS feature type)... if you vectorized your circles into circular polygons, you would be 90% of the way there. If you didn't mind querying using a bounding-box instead of a circle you'd be 100% of the way there. > Opinions would be appreciated -- I looked at the online docs, but could > not find any discussion about optimizing this kind of data schema and > query requirement. -- __ / | Paul Ramsey | Refractions Research | Email: pramsey@refractions.net | Phone: (250) 885-0632 \_
Bryan Field-Elliot <bryan_lists@netmeme.org> writes: > I'm developing a GIS system on PostgreSQL. I need to store a table of > points (latitude and longitude), and do queries such as "return all > points enclosed with the given circle ((X,Y),R)". You might care to look at http://postgis.refractions.net/postgis-0.5.1.tar.gz and see if you're not reinventing the wheel. regards, tom lane
On Thu, 2 Aug 2001, Bryan Field-Elliot wrote: > I'm developing a GIS system on PostgreSQL. I need to store a table of > points (latitude and longitude), and do queries such as "return all > points enclosed with the given circle ((X,Y),R)". > > The "point" data type looks convenient, however, it doesn't appear to be > indexable. > > Can anyone tell me what the preferred, recommended strategy for this > design pattern is, with respect to PostgreSQL? One which makes good use > of indices, if possible (realizing that I personally don't know a good > way to index for a radius search). > > For extra credit, it would be nice if I could store circles instead of > points, and issue queries like the following: > > "given a circle ((X,Y),R), return all circles which share some common area" > I haven't yet played with it, but look at PostGIS, an add-on for PostgreSQL which claims to have better spatial indexing & querying capabilities for geometric datatypes. As an aside, can anyone comment on the likelihood of this becoming a part of mainstream pgsql? Or what is currently happening with pgsql spatial support, especially regarding fmaps development? Cheers, Brent Wood
> > For extra credit, it would be nice if I could store circles instead of > > points, and issue queries like the following: > > > > "given a circle ((X,Y),R), return all circles which share some common area" > > > > I haven't yet played with it, but look at PostGIS, an add-on for > PostgreSQL which claims to have better spatial indexing & querying > capabilities for geometric datatypes. > > As an aside, can anyone comment on the likelihood of this becoming a part > of mainstream pgsql? Or what is currently happening with pgsql spatial > support, especially regarding fmaps development? PostGIS is sitting in my mailbox and I am waiting to hear if it should be added to contrib. It is 132k. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026