Обсуждение: 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
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
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 > >
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!
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! > > > > > > >
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 > > >
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 > > >
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 > > >
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
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
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