dputler (11) [Avatar] Offline
#1
I'm working through Chapter 8 at the moment, but ran into problems with the San Francisco Bridges, and SF Bay Area city data sql files. I'm not getting errors when using i ~/path/to/sf.bridges.sql (or sf.cities.sql) from the psql command line, but I'm not getting a geometry column registered. Trying to figure this out, I moved on to the Chapter 10 data within the postgis_in_action_data.zip, and sourced the ch10_data.sql file. The US Interstates read in correctly, but the Spanish nuclear power plant locations don't. The problem with this code comes at line 10383 of the file. The SELECT AddGeometryColumn call is missing an argument (specifically 'the_geom'). Looking through the two files for Chapter 8, I noticed that there are no calls to AddGeometryColumn, which could be the issue with them.
dputler (11) [Avatar] Offline
#2
Re: A few more issues with the data archive
Okay, I've altered the sql file to read in the bridges multilinestrings with minimal headaches, but my attempt to do with the city multipolygons ran into problems (it would hang in the middle of the inserts for no apparent reason, perhaps a timeout issue). I ultimately gave up and downloaded the original city multipolygon shapefile set from DataSF.org and read it into PostgreSQL using shp2pgsql, which seems to work much better. Below is the redone sql to read in the bridge data:

CREATE SCHEMA sf;

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = sf, pg_catalog, public;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE bridges (
gid serial primary key,
objectid integer,
id integer,
bridge_nam character varying(50)
);

SELECT AddGeometryColumn('sf', 'bridges', 'the_geom', 2227, 'MULTILINESTRING', 2);

SELECT pg_catalog.setval('bridges_gid_seq', 5, false);

INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (2, 2, 0, 'Third Street Bridge', '0105000020B3080000010000000102000000020000004F35403C08F356419108B23D8F0540414FA4C11606F35641C7446F64E9054041');
INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (3, 3, 0, 'Golden Gate Bridge', '0105000020B30800000100000001020000000200000077B3CCAB0BDA56415C8D119042344041F76F24AFB6D9564193A2BA082F3E4041');
INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (4, 4, 0, 'San Francisco Bay Bridge - East', '0105000020B3080000010000000102000000080000003137A589D3F95641B4A4C8E129324041ED02EBE186FA5641A62064FCA5334041FE94F091E4FA5641D47B4F88363440419442E6D72CFB5641D7B98E308C3440416E982E358DFB5641382BC723D7344041FD4255A1C6FF56411C58B3F1143840419EE4D4F171005741C0596F3F653840410AB4016DB20357415164D26696394041');
INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (1, 1, 0, 'San Francisco Bay Bridge - West', '0105000020B3080000010000000102000000030000006D50C1CA47F356411724A2475C23404167736230CEF356418289645B83244041F2AA94DD65F9564156B0B23D38314041');

CREATE INDEX idx_bridges_the_geom ON bridges USING gist (the_geom);
dputler (11) [Avatar] Offline
#3
Re: A few more issues with the data archive
A PS to my last post:

The sql file for the SF bridge data in the data archive labels the bridge name field as 'bridge_nam', while the example queries in Chapter 8 use 'bridge' as the field name.
regina.leo (265) [Avatar] Offline
#4
Re: A few more issues with the data archive
Thanks for the input. We'll check this out why that didn't work. I think we migh have changed the column name midway because we kept on typing it in wrong (with the e), so just changed it to bridge.

Which version of PostgreSQL were you using and on what Operating System?
dputler (11) [Avatar] Offline
#5
Re: A few more issues with the data archive
I'm using PostgreSQL 8.4.4 on 64bit Ubuntu 10.04. I'm not using the postgres account for this, but one under by own login ID, I'm not sure if that creates a problem in terms of getting the geometry column registered within the DB (which belongs to my ID) under the original sql code (since it explicitly gives ownership to postgres). In terms of the premature ending of the insert of the city records, that is really perplexing. It seems like it is having parse errors. If I manually feed it one record at a time (doing a cut and paste from the script file in a text editor, and pasting it into a psql session command prompt) things work. When I feed it script, the read error occurs.
regina.leo (265) [Avatar] Offline
#6
Re: A few more issues with the data archive
That is strange. I wonder if we introduced a white space character or its the character line breaks tripping up the parser.

We built the file on windows so I guess that's possible. I'll try it on my OpenSuse VM and see what happens.

As long as the account has rights to insert into geometry_columns and run the PostGIS functions, that is all it should need.

Thanks,
Regina
dputler (11) [Avatar] Offline
#7
Re: A few more issues with the data archive
One thing you may want to do is make sure the public schema is always on the search path in the sql scripts that create a data table. I had a minor problem with this for the world cities data. Many folks are likely to have created the DB within the public schema, so they will have problems with the geometry column of a new table if public schema isn't in their search path.