Opened 3 years ago

Closed 3 years ago

#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

May also have effect on other installations and quickstarts

Change history (9)

comment:1 by darkblueb, 3 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, 3 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:4 by astrid_emde, 3 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:6 by astrid_emde, 3 years ago

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

comment:8 by astrid_emde, 3 years ago

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

comment:9 by astrid_emde, 3 years ago

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.