Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
Hi, A good start is to check what has been already done in the domain of gazetteers (http://en.wikipedia.org/wiki/Gazetteer). You will probably find across the list some data and schemas (with hierachical structure) that should fit your needs. Marc-André Morin De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de pcr...@pcreso.com Envoyé : 10 avril 2012 02:22 À : Michal Kubenka Cc : postgis-users@postgis.refractions.net Objet : Re: [postgis-users] How to design a database for continents,countries, regions, cities and POIs? Hi Michal, One suggestion... There are two ways (at least :-) to do this in a RDBMS. You can have the spatial relationship implicit in the feature geometries, so a spatial query is used, for example, to determine the cities within a country: select * from polygons a, polygons b where a.type = 'city' and b.type='country' and b.name='Italy'; While flexible effective, relying on spatial queries for quick searches with polygons with many thousands, or even millions of records may not be ideal. The other approach is to explicitly predefine these relationships, so a column for each polygon feature stores the parent id. Simplistically assuming the parent of a city is the country containing it, rather than navigating the hierarchy, the above query becomes: select * from polygons where type=city and parent_id = (select id from polygons where type = 'country' and name = 'Italy'); Even with both structures optimised indexed, the latter is likely to be much faster. No join is required. Given the country containing a city is a pretty static relationship, I suggest predefining to optimise query performance makes sense. If you store the heirarchies as predefined levels then a heirarchical search using the recursive with capability- see: http://www.postgresql.org/docs/8.4/static/queries-with.html is perhaps possible, to invoke searches up down the tree. So use Postgis to determine the parent id using a spatial function, then store this as an indexed id. HTH, Brent Wood I'd say there are several approaches you could take to build a viable database, the optimal one is defined by your use case: the sorts of queries you want to apply. --- On Tue, 4/10/12, Michal Kubenka mkube...@gmail.com wrote: From: Michal Kubenka mkube...@gmail.com Subject: Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs? To: pcr...@pcreso.com Cc: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Tuesday, April 10, 2012, 9:59 AM Actually what we need is some hierarchical base for relationship between countries, cities, regions, etc. Main goal of the application will be collecting data from many sources about specific cities, regions, countries and so on, and store it in database. Let's say we will have city Rome, we collect some info about this city into database from couple sources. And we need to know that Rome is in province Rome, sub-region Lazio in region Lazio, country Italy. So system should be flexible to allow create such relation from real world. That's why I would choose two tables: 1) `polygons` - which can store countries, regions, sub-regions, provinces etc. 2) `points` - which can store cities and POIs Thanks. Michal K. On Mon, Apr 9, 2012 at 8:11 PM, pcr...@pcreso.com wrote: Are you planning to store multiple versions of these polygons, for zoom layers? Generally you need a high res version (eg: coastline) when zoomed in (large scale) and a lower resolution version when zoomed out (you can't see don't need the detail. This may or may not have an impact on your eventual data model, but it is worth ensuring you take this into account during the data modeling process. You can have a model where each feature has multiple geometry columns associated with it in the one table, or an approach which has the geometries in separate tables, using ID's to link to the aspatial attributes. The former is a simpler, monolithic solution, the latter is more complex but allows more use of tablespaces underlying Postgres optimisation. You may also find you need to carry out joins (identify relationships between types of polygon, eg: cities within counties within states within countries, and this may perform better with a denormalised structure with separate tables for different categories of polygon. One example you might look at is the OSM data model. Not quite what you are describing, but a robust well tested model for global roads related spatial data, which does not use Postgis at all. http://booki.flossmanuals.net/openstreetmap/_draft/_v/1.0/the-osm-data-model/ --- On Mon, 4/9/12, mkubenka
Re: [postgis-users] shp2pgsql in a bash script question
The easiest way is to lauch pgAdmin and connect to the server. At the first
time, a little window will be prompted asking you password for the user
postgres, and once you will have entered this password, just check the option
Store password... And you will never be asked to enter this password anymore
when you will launch your batch file.
Hope this help,
Marc-André
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Carl
Anderson
Envoyé : 7 mai 2011 17:43
À : PostGIS Users Discussion
Objet : Re: [postgis-users] shp2pgsql in a bash script question
You would only have to enter the password once if you changed your script like
this.
#!/bin/bash
(
for dir in BG CD CONCITY COUNTY COUSUB LINEARWATER PRIMARYROADS PRISECROADS
RAILS ROADS STATE SUBMCD TABBLOCK
do
ldir=$(echo ${dir} | tr 'A-Z' 'a-z') download.log
shp2pgsql -s 4326 -p ${dir}/tl_2010_01_${ldir}10 public.${ldir}
done ) | psql -h localhost -d geotest -U uname -W load.log
C.
On Sat, May 7, 2011 at 4:12 PM, Dheeraj Chand dhee...@dheerajchand.com wrote:
:I have the world's dumbest question today. I am running a bash script
that goes through several directories (and each of their several
subdirectories) of shapefiles to run shp2pgsql and put them into the correct
PSQL schema and table. I'd like to be able to set it and forget it, so to
speak, but each time, the shell is prompting me for the user's password. Is
there a way to hardcode the password in?
[4:06pm]
#!/bin/bash
for dir in BG CD CONCITY COUNTY COUSUB LINEARWATER PRIMARYROADS
PRISECROADS RAILS ROADS STATE SUBMCD TABBLOCK
do
ldir=$(echo ${dir} | tr 'A-Z' 'a-z') download.log
shp2pgsql -s 4326 -p ${dir}/tl_2010_01_${ldir}10 public.${ldir} | psql
-h localhost -d geotest -U uname -W pass load.log
done
[4:07pm]
Here is the script: http://pastebin.com/prWLzEtk:
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Carl Anderson, GISP
cander...@spatialfocus.com
carl.ander...@vadose.org
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] drawing nautical data on projection 4326[solution?]
Hi Dave, You are certainly not on the right track, because the SRS EPSG:4326 is not a projection system, but more a datum (WGS84), based on an ellipsoid (GRS80). In a nutshell, the 4326 srs is a spherical geographic system and that is why the unit of measurement is in degree. So when you store your data in EPSG:4326, that means your data will have to be stored in latitude and longitude, and you cannot represent lat-long coordinates directly on a map, unless these coordinates are projected in a cartesian (X and Y) coordinate system, like the Google Mercator projection (EPSG:900913). That said, you specified in your first mail that your were working with lines measured in nautilcal miles, within a grid graduated for 0 to 100. That means that you are already in a projected coordinate system. Now, we have to figure out what is this coordinate system. Maybe I am wrong, but your 0-100 grid seems to represent a cartesian coordinate system in GRAD (not in degree)... Could you check and confirm that at first? Also, a screenshot of your map would be helpful... Regards, Marc-André De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Dave Envoyé : 29 avril 2011 04:05 À : PostGIS Users Discussion Objet : Re: [postgis-users] drawing nautical data on projection 4326[solution?] I think I have a solution to this issue I am going to convert my point origin to meter based projection (eg google 900913), create my line data, then transform it back to the 4326 projection, in this way the scale factors calculations should be done by the proj library. Does this sound like a decent solution? On 28/04/2011 10:48, Dave wrote: I have some line data measured in nautical miles. I want to draw these points on a map using the 4326 projection, the srs entry for this projection gives the unit of measurement as degree. A nautical mile is one minture of an arc of lattitude ie 0-60, the projection is measured in 0-100. So does this mean that when I want to draw a line measured in nautical miles, I have map 0-60 on to 0-100 grid , ie I have to apply 0.6 factor ? So a line starting at lat 1.2 and ending at lat 2.7 becomes a line 1.12 to 2.42 ? regards Dave. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] MULTILINESTRING to MULTIPOLYGON
Hi, This is the recipe I found in the past to get it done: http://postgis.refractions.net/pipermail/postgis-users/2008-May/019901.html It worked well for me. Regards, Marc-André De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Javier Perez Envoyé : March 25, 2011 7:35 AM À : postgis-users@postgis.refractions.net Objet : [postgis-users] MULTILINESTRING to MULTIPOLYGON Hi!, In my app I get a MULTILINESTRING and want to insert it as MULTIPOLYGON. I think there's an easy way but I did't found, can anyone bring some light? Thanks in advance ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
