Context Navigation

← Previous Ticket
Next Ticket →

#2293 closed task (fixed)

natural_earth2 database import: change geometry column name to geom instead of the_geom

Reported by:	astrid_emde	Owned by:	osgeolive@…
Priority:	normal	Milestone:
Component:	OSGeoLive	Keywords:
Cc:

Description

by default shp2pgsql will use geom instead of the_geom as geometry column name. We could change our script to adopt this new behaviour.

Has to be changed in

https://github.com/OSGeo/OSGeoLive/blob/master/bin/load_gisdata.sh#L201

May also have effect on other installations and quickstarts

Change History (9)

comment:1 by darkblueb, 4 years ago

existing data files :

user@ol14b187:~/ne21$ tar xf all_10m_20.tgz 
user@ol14b187:~/ne21$ ls
all_10m_20.tgz                              ne_10m_lakes/
ne_10m_admin_0_countries/                   ne_10m_land/
ne_10m_admin_1_states_provinces/            ne_10m_ocean/
ne_10m_geography_marine_polys/              ne_10m_populated_places_simple/
ne_10m_geography_regions_elevation_points/  ne_10m_rivers_lake_centerlines/
ne_10m_geography_regions_points/            ne_10m_urban_areas/
ne_10m_geography_regions_polys/
user@ol14b187:~/ne21$ ls -lh *
-rw-rw-r-- 1 user user  47M Nov 26  2012 all_10m_20.tgz

ne_10m_admin_0_countries:
total 11M
-rw-rw-r-- 1 user user 2.4M Nov 25  2012 ne_10m_admin_0_countries.dbf
-rw-rw-r-- 1 user user  145 Nov 25  2012 ne_10m_admin_0_countries.prj
-rw-r--r-- 1 user user  30K Nov 25  2012 ne_10m_admin_0_countries.README.html
-rw-rw-r-- 1 user user 8.4M Nov 25  2012 ne_10m_admin_0_countries.shp
-rw-rw-r-- 1 user user 2.1K Nov 25  2012 ne_10m_admin_0_countries.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_admin_0_countries.VERSION.txt

ne_10m_admin_1_states_provinces:
total 28M
-rwxr-xr-x 1 user user 7.6M Nov 25  2012 ne_10m_admin_1_states_provinces_shp.dbf*
-rwxr-xr-x 1 user user  145 Nov 25  2012 ne_10m_admin_1_states_provinces_shp.prj*
-rw-r--r-- 1 user user  37K Nov 25  2012 ne_10m_admin_1_states_provinces_shp.README.html
-rwxr-xr-x 1 user user  20M Nov 25  2012 ne_10m_admin_1_states_provinces_shp.shp*
-rwxr-xr-x 1 user user  29K Nov 25  2012 ne_10m_admin_1_states_provinces_shp.shx*
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_admin_1_states_provinces_shp.VERSION.txt

ne_10m_geography_marine_polys:
total 1.4M
-rw-rw-r-- 1 user user 262K Nov 25  2012 ne_10m_geography_marine_polys.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_geography_marine_polys.prj
-rw-r--r-- 1 user user  32K Nov 25  2012 ne_10m_geography_marine_polys.README.html
-rw-rw-r-- 1 user user 1.1M Nov 25  2012 ne_10m_geography_marine_polys.shp
-rw-rw-r-- 1 user user 2.6K Nov 25  2012 ne_10m_geography_marine_polys.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_geography_marine_polys.VERSION.txt

ne_10m_geography_regions_elevation_points:
total 672K
-rw-r--r-- 1 user user 602K Nov 25  2012 ne_10m_geography_regions_elevation_points.dbf
-rw-r--r-- 1 user user  143 Nov 25  2012 ne_10m_geography_regions_elevation_points.prj
-rw-r--r-- 1 user user  32K Nov 25  2012 ne_10m_geography_regions_elevation_points.README.html
-rw-rw-r-- 1 user user  20K Nov 25  2012 ne_10m_geography_regions_elevation_points.shp
-rw-rw-r-- 1 user user 5.7K Nov 25  2012 ne_10m_geography_regions_elevation_points.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_geography_regions_elevation_points.VERSION.txt

ne_10m_geography_regions_points:
total 224K
-rw-r--r-- 1 user user 170K Nov 25  2012 ne_10m_geography_regions_points.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_geography_regions_points.prj
-rw-r--r-- 1 user user  32K Nov 25  2012 ne_10m_geography_regions_points.README.html
-rw-rw-r-- 1 user user 6.6K Nov 25  2012 ne_10m_geography_regions_points.shp
-rw-rw-r-- 1 user user 2.0K Nov 25  2012 ne_10m_geography_regions_points.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_geography_regions_points.VERSION.txt

ne_10m_geography_regions_polys:
total 3.4M
-rw-rw-r-- 1 user user 677K Nov 25  2012 ne_10m_geography_regions_polys.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_geography_regions_polys.prj
-rw-r--r-- 1 user user  32K Nov 25  2012 ne_10m_geography_regions_polys.README.html
-rw-rw-r-- 1 user user 2.7M Nov 25  2012 ne_10m_geography_regions_polys.shp
-rw-rw-r-- 1 user user 8.4K Nov 25  2012 ne_10m_geography_regions_polys.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_geography_regions_polys.VERSION.txt

ne_10m_lakes:
total 3.7M
-rw-r--r-- 1 user user 1.4M Nov 25  2012 ne_10m_lakes.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_lakes.prj
-rw-r--r-- 1 user user  37K Nov 25  2012 ne_10m_lakes.README.html
-rw-r--r-- 1 user user 2.2M Nov 25  2012 ne_10m_lakes.shp
-rw-r--r-- 1 user user  11K Nov 25  2012 ne_10m_lakes.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_lakes.VERSION.txt

ne_10m_land:
total 6.4M
-rw-r--r-- 1 user user  134 Nov 25  2012 ne_10m_land.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_land.prj
-rw-r--r-- 1 user user  28K Nov 25  2012 ne_10m_land.README.html
-rw-r--r-- 1 user user 6.3M Nov 25  2012 ne_10m_land.shp
-rw-r--r-- 1 user user  108 Nov 25  2012 ne_10m_land.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_land.VERSION.txt

ne_10m_ocean:
total 6.4M
-rw-r--r-- 1 user user  134 Nov 25  2012 ne_10m_ocean.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_ocean.prj
-rw-r--r-- 1 user user  28K Nov 25  2012 ne_10m_ocean.README.html
-rw-rw-r-- 1 user user 6.3M Nov 25  2012 ne_10m_ocean.shp
-rw-rw-r-- 1 user user  108 Nov 25  2012 ne_10m_ocean.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_ocean.VERSION.txt

ne_10m_populated_places_simple:
total 30M
-rw-r--r-- 1 user user  29M Nov 25  2012 ne_10m_populated_places.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_populated_places.prj
-rw-r--r-- 1 user user  32K Nov 25  2012 ne_10m_populated_places.README.html
-rw-r--r-- 1 user user 201K Nov 25  2012 ne_10m_populated_places.shp
-rw-r--r-- 1 user user  58K Nov 25  2012 ne_10m_populated_places.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_populated_places.VERSION.txt

ne_10m_rivers_lake_centerlines:
total 8.9M
-rw-r--r-- 1 user user 1.4M Nov 25  2012 ne_10m_lakes.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_lakes.prj
-rw-r--r-- 1 user user  37K Nov 25  2012 ne_10m_lakes.README.html
-rw-r--r-- 1 user user 2.2M Nov 25  2012 ne_10m_lakes.shp
-rw-r--r-- 1 user user  11K Nov 25  2012 ne_10m_lakes.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_lakes.VERSION.txt
-rw-rw-r-- 1 user user 1.2M Nov 25  2012 ne_10m_rivers_lake_centerlines.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_rivers_lake_centerlines.prj
-rw-r--r-- 1 user user  38K Nov 25  2012 ne_10m_rivers_lake_centerlines.README.html
-rw-rw-r-- 1 user user 4.0M Nov 25  2012 ne_10m_rivers_lake_centerlines.shp
-rw-rw-r-- 1 user user  12K Nov 25  2012 ne_10m_rivers_lake_centerlines.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_rivers_lake_centerlines.VERSION.txt

ne_10m_urban_areas:
total 20M
-rw-rw-r-- 1 user user 859K Nov 25  2012 ne_10m_urban_areas.dbf
-rw-r--r-- 1 user user  147 Nov 25  2012 ne_10m_urban_areas.prj
-rw-r--r-- 1 user user  29K Nov 25  2012 ne_10m_urban_areas.README.html
-rw-rw-r-- 1 user user  19M Nov 25  2012 ne_10m_urban_areas.shp
-rw-rw-r-- 1 user user  93K Nov 25  2012 ne_10m_urban_areas.shx
-rw-r--r-- 1 user user    5 Nov 25  2012 ne_10m_urban_areas.VERSION.txt

comment:2 by darkblueb, 4 years ago

passing attempt at new data:

 $pg_dump natural_earth2 | sed -e 's/the_geom/geom/g' > ne2_geom.sql

 $createdb natural_earth21
 $psql natural_earth21 -c 'create extension postgis'
 $psql -f ne2_geom.sql natural_earth21
 $pg_dump -Fc natural_earth21 > ne21.dump   ## restore will generate non-fatal errors

 $pg_dump natural_earth21 -t ne_10m_admin_0_countries -t \
   ne_10m_admin_1_states_provinces_shp -t ne_10m_geography_marine_polys -t \ 
   ne_10m_geography_regions_elevation_points -t ne_10m_geography_regions_points -t \
   ne_10m_geography_regions_polys -t ne_10m_lakes -t ne_10m_land -t ne_10m_ocean -t \
   ne_10m_populated_places -t ne_10m_rivers_lake_centerlines -t ne_10m_urban_areas > 
   ne21_tables.sql

 $bzip2 ne21_tables.sql

comment:3 by darkblueb, 4 years ago

new data here

https://download.osgeo.org/livedvd/data/natural_earth2/ne21_tables.sql.bz2

https://download.osgeo.org/livedvd/data/natural_earth2/ne21.dump

comment:4 by astrid_emde, 4 years ago

We only have to change the row: https://github.com/OSGeo/OSGeoLive/blob/master/bin/load_gisdata.sh#L201

-g the_geom -> new: -g geom

  shp2pgsql -W LATIN1 -s 4326 -I -g geom "$n" | \
     sudo -u $POSTGRES_USER psql --quiet natural_earth2

comment:5 by darkblueb, 4 years ago

https://github.com/OSGeo/OSGeoLive/pull/303

comment:6 by astrid_emde, 4 years ago

The PR was merged. We now have to check the quickstarts, wether they use the old column_name

comment:7 by astrid_emde, 4 years ago

We have to fix:

the_geom in https://github.com/OSGeo/OSGeoLive-doc/blob/master/doc/quickstart/t-rex_quickstart.rst

comment:8 by astrid_emde, 4 years ago

Fix t-rex: Geometry column name in PR: https://github.com/OSGeo/OSGeoLive-doc/pull/658

comment:9 by astrid_emde, 4 years ago

Resolution:	→ fixed
Status:	new → closed

Note: See TracTickets for help on using tickets.

Download in other formats: