[postgis-users] Installing PostGIS-1.4.1
Hello,
i downloaded and installed the prerequisites for PostGIS, started postgresql
(8.4). I work on linux, OpenSuSE 11.2.
After starting PostgreSQL i created a database "gis":
createdb -E UTF8 -O tmohr gis
createlang plpgsql gis
Then i tried to prepare that DB further:
pgsql -f postgis/postgis.sql -d gis
This leads to the warnings that i attached below.
I'm not sure now if everything went ok or (if not) what i need to change.
The following import of the planet.osm fails:
postg...@schleim:/local/ftp/osm> osm2pgsql -s planet-090408.osm.bz2
osm2pgsql SVN version 0.69-19517
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
HINWEIS: Tabelle »planet_osm_point« existiert nicht, wird übersprungen
HINWEIS: Tabelle »planet_osm_point_tmp« existiert nicht, wird übersprungen
SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );
failed: FEHLER: AddGeometryColumns() - invalid SRID
CONTEXT: SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 ,
$5 )"
PL/pgSQL-Funktion »addgeometrycolumn« Zeile 5 bei SQL-Anweisung
Error occurred, cleaning up
I found several pieces of descriptions of how to set up a posgis server, not
sure if i did all necessary steps.
If anybody could give me a hint on what to change, that would be great.
Thanks for any hints,
Torsten.
-Warnings:
BEGIN
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:56: HINWEIS: Typ »spheroid« ist
noch nicht definiert
DETAIL: Hüllentypdefinition wird erzeugt.
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:62: HINWEIS: Rückgabetyp spheroid
ist nur eine Hülle
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:68: HINWEIS: Argumenttyp spheroid
ist nur eine Hülle
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:74: HINWEIS: Argumenttyp spheroid
ist nur eine Hülle
CREATE FUNCTION
CREATE TYPE
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:91: HINWEIS: Typ »geometry« ist
noch nicht definiert
DETAIL: Hüllentypdefinition wird erzeugt.
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:97: HINWEIS: Rückgabetyp geometry
ist nur eine Hülle
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:103: HINWEIS: Argumenttyp
geometry ist nur eine Hülle
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:109: HINWEIS: Argumenttyp
geometry ist nur eine Hülle
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:127: HINWEIS: Rückgabetyp
geometry ist nur eine Hülle
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:133: HINWEIS: Rückgabetyp
geometry ist nur eine Hülle
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:139: HINWEIS: Argumenttyp
geometry ist nur eine Hülle
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:145: HINWEIS: Argumenttyp
geometry ist nur eine Hülle
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
.
CREATE FUNCTION
CREATE FUNCTION
psql:/tmp/postgis-1.4.1/postgis/postgis.sql:326: HINWEIS: Typ »box3d« ist
noch nicht definiert
DETAIL: Hüllentypdefinition wird e
Re: [postgis-users] Installing PostGIS-1.4.1
Hello,
thanks for your answers. It is some time ago that i used SQL, but this looks
strange to me, PostgresQL only contains two empty tables:
To set up PostGIS i did:
createdb -E UTF8 -O tmohr gis
createlang plpgsql gis
psql -f postgis/postgis.sql
This lead to quite many warnings. Postgres mentions that it skips several
things. The log is below.
So i tried:
postg...@schleim:/local/ftp/osm> psql -d gis
psql (8.4.1)
Type "help" for help.
gis=# \d
List of relations
Schema | Name | Type | Owner
+--+---+--
public | geometry_columns | table | postgres
public | spatial_ref_sys | table | postgres
(2 rows)
gis=# select * from spatial_ref_sys;
srid | auth_name | auth_srid | srtext | proj4text
--+---+---++---
(0 rows)
gis=# select * from geometry_columns;
f_table_catalog | f_table_schema | f_table_name | f_geometry_column |
coord_dimension | srid | type
-++--+---+-+--+--
(0 rows)
The tables are all empty, is that ok? I fear that something went wrong here,
as osm2pgsql dies early with the warning that several tables don't exist:
postg...@schleim:/local/ftp/osm> osm2pgsql planet-090408.osm.bz2
osm2pgsql SVN version 0.69-19517
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
HINWEIS: Tabelle »planet_osm_point« existiert nicht, wird übersprungen
HINWEIS: Tabelle »planet_osm_point_tmp« existiert nicht, wird übersprungen
SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );
failed: FEHLER: AddGeometryColumns() - invalid SRID
CONTEXT: SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 ,
$5 )"
PL/pgSQL-Funktion »addgeometrycolumn« Zeile 5 bei SQL-Anweisung
Error occurred, cleaning up
Isn't that a sign that something more basic went wrong?
Thanks for any hints,
Torsten.
Am Donnerstag, 14. Januar 2010 21:36:25 schrieb Paragon Corporation:
> Torsten,
> I think your postgis install is fine. You are missing the srid 900913 in
> your spatial_ref_sys table.
>
> Just need to add it. That one is not normally installed.
>
> check http://spatialreference.org
>
> to get the insert statement to use (well I think it might be there) or
> someone knows here off-hand what the insert statement should be.
>
> Leo
>
> -Original Message-----
> From: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Torsten
> Mohr
> Sent: Thursday, January 14, 2010 3:24 PM
> To: postgis-users@postgis.refractions.net
> Subject: [postgis-users] Installing PostGIS-1.4.1
>
> Hello,
>
> i downloaded and installed the prerequisites for PostGIS, started
> postgresql (8.4). I work on linux, OpenSuSE 11.2.
>
> After starting PostgreSQL i created a database "gis":
>
> createdb -E UTF8 -O tmohr gis
> createlang plpgsql gis
>
> Then i tried to prepare that DB further:
>
> pgsql -f postgis/postgis.sql -d gis
>
>
> This leads to the warnings that i attached below.
>
> I'm not sure now if everything went ok or (if not) what i need to change.
>
> The following import of the planet.osm fails:
>
>
> postg...@schleim:/local/ftp/osm> osm2pgsql -s planet-090408.osm.bz2
> osm2pgsql SVN version 0.69-19517
>
> Using projection SRS 900913 (Spherical Mercator) Setting up table:
> planet_osm_point
> HINWEIS: Tabelle »planet_osm_point« existiert nicht, wird übersprungen
> HINWEIS: Tabelle »planet_osm_point_tmp« existiert nicht, wird übersprungen
> SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );
> failed: FEHLER: AddGeometryColumns() - invalid SRID
> CONTEXT: SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4
> , $5 )"
> PL/pgSQL-Funktion »addgeometrycolumn« Zeile 5 bei SQL-Anweisung
>
> Error occurred, cleaning up
>
>
> I found several pieces of descriptions of how to set up a posgis server,
> not sure if i did all necessary steps.
>
> If anybody could give me a hint on what to change, that would be great.
>
>
> Thanks for any hints,
> Torsten.
>
>
>
> -Warnings:
>
> BEGIN
>
> psql:/tmp/postgis-1.4.1/postgis/postgis.sql:56: HINWEIS: Typ »spheroid«
> ist noch nicht definiert
> DETAIL: Hüllentypdefinition wird erzeugt.
>
> CREATE FUNCTION
>
> psql:/tmp/postgis-1.4.1/postgis/postgis.sql:62: HINWEIS: Rückgabetyp
> spheroid ist nur eine Hülle
> CREATE FUNCTION
>
> psql:/tmp/postgis-1.4.1/postgis/postgis.sql:68: HINWEIS: Argumenttyp
&
[postgis-users] Query using ST_transform fails
Hello, (i accidentially posted this on the openstreetmap mailing list, but it belongs here:) I once got a hint on this mailing list to use a query like this to get the lat/lon of the world capitals: A) select st_X(wayLL), st_Y(wayLL), name from (select ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point where capital='yes') as foo limit 5; B) Based on that hint i used this query: select st_X(st_transform(way,4326)), st_Y(st_transform(way,4326)), name from planet_osm_point where place='city' and capital='yes'; That query worked fine and i did not change my system since then (that somehow can't be true). I now get errors for both queries: FEHLER: transform: couldn't project point (653103 6.63036e+06 0): failed to load NAD27-83 correction file (-38) TIP: PostGIS was unable to transform the point because either no grid shift files were found, or the point does not lie within the range for which the grid shift is defined. Refer to the ST_Transform() section of the PostGIS manual for details on how to configure PostGIS to alter this behaviour. Could it be that due to an RPM update of PostgreSQL some scripts need to be reinstalled? I can still generate maps using mapnik. What do i need to do to make those queries work again? In a second step i'd like to extract the polygons that make up the borders of a country as lat/lon values. I wonder if this is possible by just applying the SQL query above to the way of a polygon? Or is there another way to get the data i want? Thanks for any hints, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query using ST_transform fails
Am Dienstag, 2. November 2010 22:48:08 schrieb Mike Toews: > select distinct st_srid(way) from planet_osm_point; > Hello, i have to say i don't know exactly, but placing that query i got: gis=> select distinct st_srid(way) from planet_osm_point; st_srid - 900913 (1 Zeile) Best regards, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query using ST_transform fails
Hello, thanks for your help. Regarding your questions: No, i don't use www.pgrpms.org, my system is openSuse 11.2, i installed their postgresql, which is version 8.4.4. Yes, i installed Proj4 from source. I have to say i'm not sure any more if i installed Proj-datumgrid in the /nad subdirectory of Proj 4 before configuring, but in /usr/local/share/proj i got: -rw-r--r-- 1 root root694 10. Jan 2010 GL27 -rw-r--r-- 1 root root 6385 10. Jan 2010 nad.lst -rw-r--r-- 1 root root 19501 10. Jan 2010 nad27 -rw-r--r-- 1 root root 16559 10. Jan 2010 nad83 -rw-r--r-- 1 root root 7043 10. Jan 2010 world -rw-r--r-- 1 root root261 10. Jan 2010 proj_def.dat -rw-r--r-- 1 root root 551012 10. Jan 2010 epsg -rw-r--r-- 1 root root 453436 10. Jan 2010 esri -rw-r--r-- 1 root root 76843 10. Jan 2010 esri.extra -rw-r--r-- 1 root root 3702 10. Jan 2010 other.extra -rw-r--r-- 1 root root 77820 10. Jan 2010 IGNF Would it help to re-install Proj 4 ? Would you expect problems doing so as i installed PostGis after Proj 4 ? Best regards, Torsten. Am Dienstag, 2. November 2010 22:34:05 schrieb Mike Toews: > It appears the grid shift files are missing. These are not necessarily > installed by default. > > Are you using http://www.pgrpms.org/ ? Is proj-nad installed? Did you > install proj.4 from source? If so, did you get the ZIP files and put > them in the right place before configuring? > > -Mike > > On 2 November 2010 14:09, Torsten Mohr wrote: > > Hello, > > > > (i accidentially posted this on the openstreetmap mailing list, but it > > belongs here:) > > > > > > I once got a hint on this mailing list to use a query like this to get > > the lat/lon of the world capitals: > > > > A) > > select st_X(wayLL), st_Y(wayLL), name from (select > > ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point > > where capital='yes') as foo limit 5; > > > > B) > > Based on that hint i used this query: > > select st_X(st_transform(way,4326)), st_Y(st_transform(way,4326)), name > > from planet_osm_point where place='city' and capital='yes'; > > > > That query worked fine and i did not change my system since then (that > > somehow can't be true). I now get errors for both queries: > > > > FEHLER: transform: couldn't project point (653103 6.63036e+06 0): failed > > to load NAD27-83 correction file (-38) > > TIP: PostGIS was unable to transform the point because either no grid > > shift files were found, or the point does not lie within the range for > > which the grid shift is defined. Refer to the ST_Transform() section of > > the PostGIS manual for details on how to configure PostGIS to alter this > > behaviour. > > > > > > Could it be that due to an RPM update of PostgreSQL some scripts need to > > be reinstalled? I can still generate maps using mapnik. > > > > > > What do i need to do to make those queries work again? > > > > > > In a second step i'd like to extract the polygons that make up the > > borders of a country as lat/lon values. I wonder if this is possible by > > just applying the SQL query above to the way of a polygon? Or is there > > another way to get the data i want? > > > > > > Thanks for any hints, > > Torsten. > > ___ > > 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 > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query using ST_transform fails
Hello,
> I'm not sure why proj would require the NAD gridshift files for
> converting a google (900913) projection to WGS84 long/lat. Do you get
> any errors with this:
>
> select st_X(wayLL), st_Y(wayLL)
> from (
> select ST_Transform('SRID=900913;POINT(653103 6.63036e+06 0)'::geometry,
> 4326) as wayLL
> ) as foo;
>
> -- I get 5.86692407004312; 51.0512259090808
placing your query i get:
st_x | st_y
--+--
5.86692407004312 | 51.0512259090808
(1 Zeile)
>
> If there are no errors, then there are a few other things that could
> be wrong. What do you get from:
>
> select srid, proj4text
> from spatial_ref_sys
> where srid=900913 or srid=4326;
>
here i get:
srid |proj4text
+--
4326 | +proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs
900913 | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0
+y_0=0 +units=m +k=1.0 +nadgri...@null +no_defs
(2 Zeilen)
Hmm, i wonder what is wrong with my installation. I have to admit i don't
know what happens in a row using the query i originally asked about.
Seems the data come out of PostgreSQL ok, but converting the data fails.
If the queries above ruled that out then i don't know what is the problem.
Any hints would be just great. I'm not sure what makes most sense to
re-install.
Best regards,
Torsten.
>
> You can re-install proj without touching anything else. If you have
> the source files still, then unzip the grid-shift files in the nad
> directory, then:
>
> make clean
> ./configure
> make
> make install
>
>
> -Mike
>
> On 4 November 2010 13:37, Torsten Mohr wrote:
> > Hello,
> >
> > thanks for your help.
> >
> > Regarding your questions:
> >
> > No, i don't use www.pgrpms.org, my system is openSuse 11.2, i installed
> > their postgresql, which is version 8.4.4.
> >
> > Yes, i installed Proj4 from source. I have to say i'm not sure any more
> > if i installed Proj-datumgrid in the /nad subdirectory of Proj 4 before
> > configuring, but in /usr/local/share/proj i got:
> >
> > -rw-r--r-- 1 root root694 10. Jan 2010 GL27
> > -rw-r--r-- 1 root root 6385 10. Jan 2010 nad.lst
> > -rw-r--r-- 1 root root 19501 10. Jan 2010 nad27
> > -rw-r--r-- 1 root root 16559 10. Jan 2010 nad83
> > -rw-r--r-- 1 root root 7043 10. Jan 2010 world
> > -rw-r--r-- 1 root root261 10. Jan 2010 proj_def.dat
> > -rw-r--r-- 1 root root 551012 10. Jan 2010 epsg
> > -rw-r--r-- 1 root root 453436 10. Jan 2010 esri
> > -rw-r--r-- 1 root root 76843 10. Jan 2010 esri.extra
> > -rw-r--r-- 1 root root 3702 10. Jan 2010 other.extra
> > -rw-r--r-- 1 root root 77820 10. Jan 2010 IGNF
> >
> >
> > Would it help to re-install Proj 4 ?
> >
> > Would you expect problems doing so as i installed PostGis after Proj 4 ?
> >
> >
> > Best regards,
> > Torsten.
> >
> > Am Dienstag, 2. November 2010 22:34:05 schrieb Mike Toews:
> >> It appears the grid shift files are missing. These are not necessarily
> >> installed by default.
> >>
> >> Are you using http://www.pgrpms.org/ ? Is proj-nad installed? Did you
> >> install proj.4 from source? If so, did you get the ZIP files and put
> >> them in the right place before configuring?
> >>
> >> -Mike
> >>
> >> On 2 November 2010 14:09, Torsten Mohr wrote:
> >> > Hello,
> >> >
> >> > (i accidentially posted this on the openstreetmap mailing list, but it
> >> > belongs here:)
> >> >
> >> >
> >> > I once got a hint on this mailing list to use a query like this to get
> >> > the lat/lon of the world capitals:
> >> >
> >> > A)
> >> > select st_X(wayLL), st_Y(wayLL), name from (select
> >> > ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point
> >> > where capital='yes') as foo limit 5;
> >> >
> >> > B)
> >> > Based on that hint i used this query:
> >> > select st_X(st_transform(way,4326)), st_Y(st_transform(way,4326)),
> >> > name from planet_osm_point where place='city' and capital='yes';
> >> >
> >> > That query worked fine and i did not change my system since then (that
> >> > somehow can't be true). I now get errors for both queries:
> >>
Re: [postgis-users] osm2pgsql error, out of memory, though i use -s
Hello, > I suggest that you ask on one of the OpenStreetMap lists (osm-talk or > osm-dev) as people there will be more familiar with osm2pgsql. correct, i did not think of this. Next discussions i'll put there. > You will have to start over. Some information on osm2pgsql performance > can be found here: > http://www.geofabrik.de/media/2010-07-10-rendering-toolchain-performance.pd > f but the bottom line is, more memory (along with a proper setting of -C as > has been suggested already) will definitely help. Thanks for that hint. I have changed the command line to use more cache with -C 4000. This is just a guess, i hope it works. I imported other OSM planet files before and never got that error. It seems it is purely related to the amount of data? Thanks for all the hints i got on this list, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] SQL Query to get the polygons that make up a country?
Hi, i just re-installed PostGIS and imported an OSM planet file. I'd like to formulate a query that gives me the borders of a country in latitute / longitude. I already got a query (with help from this list) to get all capitols: select st_X(wayLL), st_Y(wayLL), name from (select ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point where capital='yes') as foo limit 5; This query works quite fine. Can anybody give me a hint what would be a valid query to get one (or more !) polygons that make up a country (if that country covers two or more areas or has holes in it)? Thanks for any hints, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] searching a valid query to return the borders of a country
Hello, i'm searching for an SQL query that returns the borders of a country or better, _all_ countries. For testing i tried queries like: select name, way from planet_osm_polygon where admin_level <> '' and name='Ireland'; This returns one Polygon that, when drawn, looks like Ireland. Doing the same for "Deutschland" returns six polygons, of which none looks like "Deutschland", more like some of the islands around it. What is the best way to get the borders of a country? Best regards, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] searching a valid query to return the borders of a country
Hello, thanks a lot for your answer. I'll have a look at the web page you suggested. Sorry, if my mail was off-topic here, i was not aware of that. You mentioned that before, related to another mail, that it was off-topic here. For the issue related to this mail it was not obvious to me that it is off- topic here as i was not aware of the dependency on the OSM data structure. Best regards, Torsten. Am Sonntag, 5. Dezember 2010, 18:26:58 schrieb Frederik Ramm: > Hi, > > Torsten Mohr wrote: > > i'm searching for an SQL query that returns the borders of a country or > > better, _all_ countries. > > The correct SQL query depends on the data structure in your data base. > > If your data has been imported from OpenStreetMap then the answer to > your question requires a lot of knowledge about the OpenStreetMap data > model and how osm2pgsql works. > > You are on the wrong mailing list. I believe explained this to you already. > > To solve your problem, I suggest to import one of the shape files from > http://www.naturalearthdata.com/downloads/10m-cultural-vectors/. The > first file in that list, "Admin 0 - Countries" will give you a nice > polygon for every country in the world. > > Bye > Frederik ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Setting up PostGis, fail at applying 900913.sql
Hi, i just tried to install the latest Postgis from scratch and fail at applying 900913.sql to my database. At least this is where i get the error, maybe the failure happens before, i'm not sure about that. I successfully installed: - proj.4.7.0 with proj-datumgrid-1.5 - geos-3.3.1 - postgis-1.5.3 Then i created the PostgreSQL database and initialised it I use PostGreSQL 9.0: In /usr/share/postgres/contrib/postgis-1.5: psql -d gis -f postgis.sql psql -d gis -f spatial_ref_sys.sql psql -d gis createuser -Upostgres -S -D -R tmohr GRANT ALL on geometry_columns TO tmohr GRANT ALL on spatial_ref_sys TO tmohr GRANT ALL ON SCHEMA PUBLIC TO tmohr I continued to install: - gdal-1.8.1 - mapnik-2.0.0 => INPUT_PLUGINS=postgis,gdal,ogr,shape,raster All goes fine so far, at least there are no error messages. In the database "gis" i have two tables now: gis-# \dt Liste der Relationen Schema | Name | Typ | Eigentümer +--+-+ public | geometry_columns | Tabelle | postgres public | spatial_ref_sys | Tabelle | postgres (2 Zeilen) When i then try to apply 900913.sql to the database: postgres@schleim:/tmp/osm2pgsql/osm2pgsql> psql -d gis -f 900913.sql psql:900913.sql:1: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »spatial_ref_sys_pkey« DETAIL: Schlüssel »(srid)=(900913)« existiert bereits. Some points: - I'm not sure if there shouldn't be more tables already in there? - When creating the language plpgsql i get the warning that it already exists, i get no other warnings during the commands above. - When dropping the dbase and not executing spatial_ref_sys.sql, all goes fine, except that importing planet.osm fails as there exists no tables: - planet_osm_point, planet_osm_line, planet_osm_polygon, . etc. I'm not sure how to proceed, can anybody give me a hint on what goes wrong? At what step should the tables planet_osm_* exist? Thanks for any hints, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] importing planet_110928.osm, fail after 5 days
Hello, i just set up PostGis and imported OSM data. it took 5 days and failed at the end, out of memory. Does "out of memory" mean RAM or can it also mean disk space? I have 8 Gig of RAM, so i wonder what the requirements for importing OSM data are. Can anybody share some hints on how to import the latest planet_*.osm? What resources should i have? Is there some way i can import with my PC? ### LOG postgres@schleim:/tmp/osm2pgsql/osm2pgsql> bzcat /local/ftp/osm3/planet-110928.osm.bz2 | osm2pgsql -d gis -S default.style -m - s -C 4000 - osm2pgsql SVN version 0.80.0 (32bit id space) Using projection SRS 900913 (Spherical Mercator) Setting up table: planet_osm_point HINWEIS: Tabelle »planet_osm_point« existiert nicht, wird übersprungen HINWEIS: Tabelle »planet_osm_point_tmp« existiert nicht, wird übersprungen Setting up table: planet_osm_line HINWEIS: Tabelle »planet_osm_line« existiert nicht, wird übersprungen HINWEIS: Tabelle »planet_osm_line_tmp« existiert nicht, wird übersprungen Setting up table: planet_osm_polygon HINWEIS: Tabelle »planet_osm_polygon« existiert nicht, wird übersprungen HINWEIS: Tabelle »planet_osm_polygon_tmp« existiert nicht, wird übersprungen Setting up table: planet_osm_roads HINWEIS: Tabelle »planet_osm_roads« existiert nicht, wird übersprungen HINWEIS: Tabelle »planet_osm_roads_tmp« existiert nicht, wird übersprungen Allocating node cache in one big chunk Mid: pgsql, scale=100, cache=4000MB, maxblocks=512001*8192 Setting up table: planet_osm_nodes HINWEIS: Tabelle »planet_osm_nodes« existiert nicht, wird übersprungen HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »planet_osm_nodes_pkey« für Tabelle »planet_osm_nodes« Setting up table: planet_osm_ways HINWEIS: Tabelle »planet_osm_ways« existiert nicht, wird übersprungen HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »planet_osm_ways_pkey« für Tabelle »planet_osm_ways« Setting up table: planet_osm_rels HINWEIS: Tabelle »planet_osm_rels« existiert nicht, wird übersprungen HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »planet_osm_rels_pkey« für Tabelle »planet_osm_rels« Reading in file: - Unknown node type 8 Processing: Node(1217841k 83.2k/s) Way(109718k 0.42k/s) Relation(1114739 7.12/s) parse time: 435067s Node stats: total(1217841367), max(1447570595) in 14632s Way stats: total(109718429), max(131557810) in 263781s Relation stats: total(1114739), max(1767603) in 156532s Going over pending ways pending_ways failed: out of memory for query result (7) Error occurred, cleaning up postgres@schleim:/tmp/osm2pgsql/osm2pgsql> ### LOG END Thanks for any hints, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Memory Bug in PostGIS Query
Hello,
i also got an "out of memory" error using PostGis (through Mapnik).
I get the error when creating a 6000 x 8000 map of Germany
with a script that worked with previous versions of PostGIS / planet_osm_*:
Traceback (most recent call last):
File "./generate_image.py", line 91, in
render(m, im)
RuntimeError: PSQL error:
out of memory for query result
Full sql was: 'SELECT AsBinary("way") AS
geom,"amenity","landuse","leisure","military","name","natural","power","tourism"
from (select * from planet_osm_polygon order by z_order,way_area desc) as
leisure WHERE "way" && SetSRID('BOX3D(503843.2677405493
5749599.546361594,1833866.038918196 7522963.241265123)'::box3d, 900913)'
Could this problem be related to the problem of the original poster?
I don't understand how a bad geometry record could get into the database.
I imported planet_osm_110928.tar.bz2, could it be related to that version?
Would it be likely that a new import of another planet_osm_* fixes the
problem? I would like to get around that, the last import took 11 days.
If not, how could i search the bad record in my database?
Best regards,
Torsten.
Am Sonntag, 23. Oktober 2011, 21:35:04 schrieb mapl...@light42.com:
> Hi -
>
> a couple of things.. it is the library GEOS that is crashing, ( part of
> PostGIS )
>
> Is your geometry clean? consistant ? what is the assurance level on that?
>
> --
>
> Next is that you have a huge, powerful computer but you are using only a
> fraction of the RAM Try reducing the Max Connections (if you are the
> primary user) to 60 from 100 then this (for example)
>
> # - Memory -
> shared_buffers = 2000MB # min 128kB
> # (change requires restart)
> temp_buffers = 64MB # min 800kB
>
> work_mem = 256MB# min 64kB
> maintenance_work_mem = 256MB# min 1MB
> --
>
> Next, upgrade your PostGIS and GEOS !!
> PostGIS 1.5.x and GEOS 3.3 I believe are current
>
> hth
> -Brian
>
> ==
> Brian Hamlin
> GeoCal
> OSGeo California Chapter
> 415-717-4462 cell
>
> ___
> 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
[postgis-users] Error in import of planet_osm
Hello, i finally managed to import the latest planet_osm file to PostGIS, but i'm not sure if it really worked that well: At the end of the import i got: Going over pending relations Pending relations took 3s at a rate of 0.00/s node cache: stored: 104835304(8.44%), storage efficiency: 99.98%, hit rate: 8.19% Stopping table: planet_osm_ways Stopping table: planet_osm_rels Stopping table: planet_osm_nodes Committing transaction for planet_osm_roads Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Sorting data and creating indexes for planet_osm_line Sorting data and creating indexes for planet_osm_roads Sorting data and creating indexes for planet_osm_polygon Committing transaction for planet_osm_point Building index on table: planet_osm_rels (fastupdate=off) Stopped table: planet_osm_nodes Sorting data and creating indexes for planet_osm_point Building index on table: planet_osm_ways (fastupdate=off) Stopped table: planet_osm_rels Indexes on planet_osm_roads created in 4315s Completed planet_osm_roads Indexes on planet_osm_point created in 4959s Completed planet_osm_point CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (FASTUPDATE=OFF); failed: FEHLER: konnte Datei »base/16384/392929.15« nicht erweitern: es wurden nur 4096 von 8192 Bytes bei Block 2072335 geschrieben HINT: Prüfen Sie den freien Festplattenplatz. ==> Translation: verify the free disk space. I did, on /mnt where postgis is running, there are 71 Gb free. Error occurred, cleaning up But i can fetch data from the data base and create maps using mapnik. Is there any step i need to redo manually? Or that i should redo to improve performance? I got PostGIS running on an SSD of 256 Gb, is that not sufficient for an import? Thanks for any hints, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Query: find the country of a location / city
Hello, i search for a SQL query that lets me find the country of a given location (mercaator latitude / longitude) or a city name. Can anybody give me a hint on how to formulate such a query? Thanks for any hints, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
