[josm-dev] PostGIS and data format?

[Jan-Benedict Glaw]

On Wed, 2008-12-24 12:51:55 -0500, Michael Speth <conzar at gmail.com> wrote:
>   Some of you suggested using PostGIS for the problem that I'm trying to
> solve.  I think you guys are right.
> You are also right that DB4O is not a good solution especially for embedded
> applications.
> 
> So my question is, does anyone know how I can access the database?  I've
> never used a spacial database but I do know how to open up connections and
> write queries like Select.

A spa*t*ial database isn't really special in any way. You still use
plain old SQL queries to access the data. The PostGIS extensions
simply add some additional operators and functions to access the
geometry columns.

> Is there a way to access the database similar to how JOSM presents the data
> (searching based on GPS coordinates)?

Sure.  To show some life example, this is the table I use for
http://webcontent.osm.lab.rfc822.org/ :

osm=> \d osm_webcontent 
   Table "public.osm_webcontent"
   Column    |   Type   | Modifiers 
-------------+----------+-----------
 node_id     | bigint   | not null
 bugtype     | integer  | not null
 description | text     | 
 geom        | geometry | 
Indexes:
    "foorunkel" gist (geom)
    "index1" btree (node_id)
Check constraints:
    "enforce_dims_geom" CHECK (ndims(geom) = 2)
    "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL)
    "enforce_srid_geom" CHECK (srid(geom) = 4326)


It contains point based information about specific locations.

This is an example SQL query, SELECTing at max. 200 rows from that
table, where the point (stored in the "geom" column) is within a given
bounding box:

osm=> select node_id, round(CAST(ST_X(geom) as numeric),7) as lon, round(CAST(ST_Y(geom) as numeric),7) as lat, description, bugtype from osm_webcontent where geom @ ST_SetSRID( ST_MakeBox2d( ST_MakePoint(8.2,47.5),ST_MakePoint(8.3,48)),4326) limit 200;

MfG, JBG

-- 
      Jan-Benedict Glaw      jbglaw at lug-owl.de              +49-172-7608481
Signature of:              Alles sollte so einfach wie möglich gemacht sein.
the second  :                          Aber nicht einfacher.  (Einstein)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.openstreetmap.org/pipermail/josm-dev/attachments/20081225/a89dba49/attachment.pgp>