Thread: Help with storing spatial (map coordinates) data?

Help with storing spatial (map coordinates) data?

From
John Tregea
Date:
Hi,

I have recently switched to PostgreSQL and had no problem bringing our
existing (my)SQL databases and data into the environment. I am now
extending the functionality of our databases and want to start storing
spatial information.

The information is made up of latitude and longitude coordinates that
define a point or location on the earth's surface. e.g. degrees, minutes
and seconds north/south and degrees, minutes and seconds east/west.

I have read up on custom data types (with input and output functions) in
the docs but am not sure if that is the best way to go. Can anyone point
me to a simple, workable implementation of storing and managing this
type of data or advise me on how to structure a series of fields that
could combine to the required string?

I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating
an X-Talk front end called Revolution for the GUI development and have
only some general experience with SQL.

Thanks in advance

John Tregea

Re: Help with storing spatial (map coordinates) data?

From
Tino Wildenhain
Date:
John Tregea schrieb:
> Hi,
>
> I have recently switched to PostgreSQL and had no problem bringing our
> existing (my)SQL databases and data into the environment. I am now
> extending the functionality of our databases and want to start storing
> spatial information.
>
> The information is made up of latitude and longitude coordinates that
> define a point or location on the earth's surface. e.g. degrees, minutes
> and seconds north/south and degrees, minutes and seconds east/west.
>
> I have read up on custom data types (with input and output functions) in
> the docs but am not sure if that is the best way to go. Can anyone point
> me to a simple, workable implementation of storing and managing this
> type of data or advise me on how to structure a series of fields that
> could combine to the required string?

I'd suggest starting w/ the contrib package and its "cube" datatype.
This datatype maintains 3d-coordinates and has long/lat input
and output. In theory if its just storing you could also just store
the longitude, latitude in numeric fields. It really depends on
what you really want to do with that data in the database.
(e.g. what kind of searches you want to do) cube datatype for example
is indexable which really helps in search queries.

Regards
Tino Wildenhain

Re: Help with storing spatial (map coordinates) data?

From
John Tregea
Date:
Hi Tino,

Thanks, I had just found the contrib directory and the "spatial_ref_sys"
file as well. The database is to manage security assessments in supply
chains and will store locations of buildings as well as points that
define transportation routes. So the data will not be searched on but
will be used to put risk assessment matrices into the correct order
along a supply chain.

I will try as you suggest and look at the "cube" datatype

Thanks for your fast reply.

Regards

John T

Tino Wildenhain wrote:
> John Tregea schrieb:
>> Hi,
>>
>> I have recently switched to PostgreSQL and had no problem bringing
>> our existing (my)SQL databases and data into the environment. I am
>> now extending the functionality of our databases and want to start
>> storing spatial information.
>>
>> The information is made up of latitude and longitude coordinates that
>> define a point or location on the earth's surface. e.g. degrees,
>> minutes and seconds north/south and degrees, minutes and seconds
>> east/west.
>>
>> I have read up on custom data types (with input and output functions)
>> in the docs but am not sure if that is the best way to go. Can anyone
>> point me to a simple, workable implementation of storing and managing
>> this type of data or advise me on how to structure a series of fields
>> that could combine to the required string?
>
> I'd suggest starting w/ the contrib package and its "cube" datatype.
> This datatype maintains 3d-coordinates and has long/lat input
> and output. In theory if its just storing you could also just store
> the longitude, latitude in numeric fields. It really depends on
> what you really want to do with that data in the database.
> (e.g. what kind of searches you want to do) cube datatype for example
> is indexable which really helps in search queries.
>
> Regards
> Tino Wildenhain
>
>

Re: Help with storing spatial (map coordinates) data?

From
"Gregory S. Williamson"
Date:
We have had good success with postGIS for storing various spatial data sets (polygons, points and lines).

They can be found at <http://postgis.refractions.net/>.

We store our data in lat/long but postGIS has many different spatial reference systems defined and I would suspect that
minutes/secondsexists. You may want to subscribe to and post your question on the postGIS mailing list. 

There are windows-ready compiled versions which seem to work well, although I've only played with them for prototypes
(ourreal database servers are all linux so I can't be of any help on the Windoze front). 

In general support for this extension of postgres is quite helpful, so I would suggest asking on their general list.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:    pgsql-general-owner@postgresql.org on behalf of John Tregea
Sent:    Sun 6/11/2006 11:18 PM
To:    pgsql-general@postgresql.org
Cc:
Subject:    [GENERAL] Help with storing spatial (map coordinates) data?

Hi,

I have recently switched to PostgreSQL and had no problem bringing our
existing (my)SQL databases and data into the environment. I am now
extending the functionality of our databases and want to start storing
spatial information.

The information is made up of latitude and longitude coordinates that
define a point or location on the earth's surface. e.g. degrees, minutes
and seconds north/south and degrees, minutes and seconds east/west.

I have read up on custom data types (with input and output functions) in
the docs but am not sure if that is the best way to go. Can anyone point
me to a simple, workable implementation of storing and managing this
type of data or advise me on how to structure a series of fields that
could combine to the required string?

I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating
an X-Talk front end called Revolution for the GUI development and have
only some general experience with SQL.

Thanks in advance

John Tregea

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

!DSPAM:448d0905111031804284693!





Re: Help with storing spatial (map coordinates) data?

From
John Tregea
Date:
Thanks Greg,

I don't know in practice if I will need the minutes and seconds, as you
say degrees with decimal information is probably more accurate. If I
store degrees in decimal I will need to convert back and forth though as
people will use GPS to enter lat and long into the system. I need to be
able to work out route lengths for various transport modes as well as
integrating (in the future) with GIS mapping software such as ESRi or
MapInfo.

I have subscribed to the Open Geospatial Consortium and downloaded their
"common architecture" which includes an SQL model... But it is a lot to
take in, so any guidance is appreciated.

Thanks and regards

John

Gregory S. Williamson wrote:
> We have had good success with postGIS for storing various spatial data sets (polygons, points and lines).
>
> They can be found at <http://postgis.refractions.net/>.
>
> We store our data in lat/long but postGIS has many different spatial reference systems defined and I would suspect
thatminutes/seconds exists. You may want to subscribe to and post your question on the postGIS mailing list. 
>
> There are windows-ready compiled versions which seem to work well, although I've only played with them for prototypes
(ourreal database servers are all linux so I can't be of any help on the Windoze front). 
>
> In general support for this extension of postgres is quite helpful, so I would suggest asking on their general list.
>
> HTH,
>
> Greg Williamson
> DBA
> GlobeXplorer LLC
>
> -----Original Message-----
> From:    pgsql-general-owner@postgresql.org on behalf of John Tregea
> Sent:    Sun 6/11/2006 11:18 PM
> To:    pgsql-general@postgresql.org
> Cc:
> Subject:    [GENERAL] Help with storing spatial (map coordinates) data?
>
> Hi,
>
> I have recently switched to PostgreSQL and had no problem bringing our
> existing (my)SQL databases and data into the environment. I am now
> extending the functionality of our databases and want to start storing
> spatial information.
>
> The information is made up of latitude and longitude coordinates that
> define a point or location on the earth's surface. e.g. degrees, minutes
> and seconds north/south and degrees, minutes and seconds east/west.
>
> I have read up on custom data types (with input and output functions) in
> the docs but am not sure if that is the best way to go. Can anyone point
> me to a simple, workable implementation of storing and managing this
> type of data or advise me on how to structure a series of fields that
> could combine to the required string?
>
> I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating
> an X-Talk front end called Revolution for the GUI development and have
> only some general experience with SQL.
>
> Thanks in advance
>
> John Tregea
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
> !DSPAM:448d0905111031804284693!
>
>
>
>
>
>
>

Re: Help with storing spatial (map coordinates) data?

From
John Tregea
Date:
Hi Brent,

I will look at postGIS today. I will try and keep the whole GIS
functionality as a separate schema to avoid confusing myself, so a
postGIS may be exactly what I am looking for.

Thanks and regards

John

Brent Wood wrote:
> On Mon, 12 Jun 2006, John Tregea wrote:
>
>
>> Hi,
>>
>> I have recently switched to PostgreSQL and had no problem bringing our
>> existing (my)SQL databases and data into the environment. I am now
>> extending the functionality of our databases and want to start storing
>> spatial information.
>>
>> The information is made up of latitude and longitude coordinates that
>> define a point or location on the earth's surface. e.g. degrees, minutes
>> and seconds north/south and degrees, minutes and seconds east/west.
>>
>> I have read up on custom data types (with input and output functions) in
>> the docs but am not sure if that is the best way to go. Can anyone point
>> me to a simple, workable implementation of storing and managing this
>> type of data or advise me on how to structure a series of fields that
>> could combine to the required string?
>>
>> I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating
>> an X-Talk front end called Revolution for the GUI development and have
>> only some general experience with SQL.
>>
>
>
> I stongly suggest you do not use tne native Postgres geometry capability,
> but install PostGIS and use this instead.
>
> See www.postgis.org
>
>
> Brent Wood
>
>
>

Re: Help with storing spatial (map coordinates) data?

From
John Tregea
Date:
Thanks Brent,

I will be cautious in my approach. The public schema is the place that I
wanted to use to store the geometry attributes, so from your points,
that sounds like the best place. The other schemas contain controlled
(security) information in proprietary data structures so I that was my
reluctance to modify those tables with the necessary geometry functions,
types etc.

Regards

John

Brent Wood wrote:
> On Tue, 13 Jun 2006, John Tregea wrote:
>
>
>> Hi Brent,
>>
>> I will look at postGIS today. I will try and keep the whole GIS
>> functionality as a separate schema to avoid confusing myself, so a
>> postGIS may be exactly what I am looking for.
>>
>>
>
> Ummm... one caution:
>
> The lovely side effect, apart from all the SQL functions to query &
> analyse spatial data in Postgres, is that any table with a
> properly created geometry attribute is automatically available as a GIS
> layer in a GIS map window, using GIS applications like QGIS, mezoGIS, JUMP
> & uDIG (even ArcInfo via the PostGIS SDE), or to a less well integrated
> extent, GRASS. It can also be a layer in a web map server application
> using something like UMN mapserver.
>
> However, not all of these support the concept of schema's, so only tables
> in the public schema may be able to be plotted/mapped.
>
> Also, from a data modelling perspective, a geometry attribute is not
> inherently different to a numeric, int, varchar or text attribute, so
> unless there is some other reason to divide entities with geometries into
> a separate schema frpom those without, I'm not sure it is good practice.
>
>
> Cheers,
>
>   Brent
>
>
>

Re: Help with storing spatial (map coordinates) data?

From
John Tregea
Date:
Hi Brent,

Excellent advice, thanks for taking the time with what must be a fairly
newbie question in GIS terms.

I appreciate your help.

Cheers

John

Brent Wood wrote:
> On Tue, 13 Jun 2006, John Tregea wrote:
>
>
>> Thanks Brent,
>>
>> I will be cautious in my approach. The public schema is the place that I
>> wanted to use to store the geometry attributes, so from your points,
>> that sounds like the best place. The other schemas contain controlled
>> (security) information in proprietary data structures so I that was my
>> reluctance to modify those tables with the necessary geometry functions,
>> types etc.
>>
>>
>
> Sounds eminently sensible :-)
>
> One point you might note, the AddGeometryColumn() function does two
> things. It adds a geometry column of the appropriate projection & type to
> the specified table. It also writes a metadata record to the
> geometry_columns table. This is where many application look to find tables
> with geometries.
>
> If you create a view on a table with a geometry column, or create a table
> with a geometry column without using the AddGeometryColumn() function (eg:
> create table foo1 as select * from foo0;), then some applications will not
> recognise the table or view as a "GIS" table.
>
> If you are adding geometries to tables via views, which it sounds like you
> may be doing, you may need to manually insert the appropriate data into
> the geometry_columns table to be fully compliant with the OGC specs &
> PostGIS implementation.
>
> If you create such a geometry table or view & the GIS package you are
> using fails to make it available as a data source, this is almost
> certainly why :-)
>
> Cheers,
>
>   Brent
>
>
>

Re: Help with storing spatial (map coordinates) data?

From
Brent Wood
Date:

On Mon, 12 Jun 2006, John Tregea wrote:

> Hi,
>
> I have recently switched to PostgreSQL and had no problem bringing our
> existing (my)SQL databases and data into the environment. I am now
> extending the functionality of our databases and want to start storing
> spatial information.
>
> The information is made up of latitude and longitude coordinates that
> define a point or location on the earth's surface. e.g. degrees, minutes
> and seconds north/south and degrees, minutes and seconds east/west.
>
> I have read up on custom data types (with input and output functions) in
> the docs but am not sure if that is the best way to go. Can anyone point
> me to a simple, workable implementation of storing and managing this
> type of data or advise me on how to structure a series of fields that
> could combine to the required string?
>
> I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating
> an X-Talk front end called Revolution for the GUI development and have
> only some general experience with SQL.


I stongly suggest you do not use tne native Postgres geometry capability,
but install PostGIS and use this instead.

See www.postgis.org


Brent Wood

Re: Help with storing spatial (map coordinates) data?

From
Brent Wood
Date:

On Tue, 13 Jun 2006, John Tregea wrote:

> Hi Brent,
>
> I will look at postGIS today. I will try and keep the whole GIS
> functionality as a separate schema to avoid confusing myself, so a
> postGIS may be exactly what I am looking for.
>

Ummm... one caution:

The lovely side effect, apart from all the SQL functions to query &
analyse spatial data in Postgres, is that any table with a
properly created geometry attribute is automatically available as a GIS
layer in a GIS map window, using GIS applications like QGIS, mezoGIS, JUMP
& uDIG (even ArcInfo via the PostGIS SDE), or to a less well integrated
extent, GRASS. It can also be a layer in a web map server application
using something like UMN mapserver.

However, not all of these support the concept of schema's, so only tables
in the public schema may be able to be plotted/mapped.

Also, from a data modelling perspective, a geometry attribute is not
inherently different to a numeric, int, varchar or text attribute, so
unless there is some other reason to divide entities with geometries into
a separate schema frpom those without, I'm not sure it is good practice.


Cheers,

  Brent

Re: Help with storing spatial (map coordinates) data?

From
Brent Wood
Date:

On Tue, 13 Jun 2006, John Tregea wrote:

> Thanks Brent,
>
> I will be cautious in my approach. The public schema is the place that I
> wanted to use to store the geometry attributes, so from your points,
> that sounds like the best place. The other schemas contain controlled
> (security) information in proprietary data structures so I that was my
> reluctance to modify those tables with the necessary geometry functions,
> types etc.
>

Sounds eminently sensible :-)

One point you might note, the AddGeometryColumn() function does two
things. It adds a geometry column of the appropriate projection & type to
the specified table. It also writes a metadata record to the
geometry_columns table. This is where many application look to find tables
with geometries.

If you create a view on a table with a geometry column, or create a table
with a geometry column without using the AddGeometryColumn() function (eg:
create table foo1 as select * from foo0;), then some applications will not
recognise the table or view as a "GIS" table.

If you are adding geometries to tables via views, which it sounds like you
may be doing, you may need to manually insert the appropriate data into
the geometry_columns table to be fully compliant with the OGC specs &
PostGIS implementation.

If you create such a geometry table or view & the GIS package you are
using fails to make it available as a data source, this is almost
certainly why :-)

Cheers,

  Brent