Re: Points, Circles, Indexes, and query-by-Radius?

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
     \_