GPSFileDepot.com
 

News:

Welcome to GPSFileDepot!

Main Menu

Issues with PostgresSQL 8.3

Started by abro, January 23, 2009, 09:39:09 PM

Previous topic - Next topic

abro

Below are some commands and responces: I tried many other commands but nothing seems to work. help please Jim

C:\Program Files\PostgreSQL\8.3\bin>Areas - shp2pgsql "C\Penna_Topo\nhd\NHD     _Area.shp"NHD_Area>nhd_area.sql
'Areas' is not recognized as an internal or external command, operable program or batch file.                                                           
C:\Program Files\PostgreSQL\8.3\bin>Areas -d shp2pgsql "C:\Penna_Topo\nhd\NHD   _Area>nhd_area.sql
'Areas' is not recognized as an internal or external command, operable program or batch file.                                                                     
C:\ProgramFiles\PostgreSQL\8.3\bin> shp2pgsql"C:\Penna_Topo\nhd\NHDArea.shp" NHD_area > nhd_area.sql
RCSID: $Id: shp2pgsql.c 2782 2008-05-27 02:59:06Z pramsey $ RELEASE: 1.3.5 USAGE: shp2pgsql [<options>] <shapefile> [<schema>.]<table>

OPTIONS:

-s <srid>  Set the SRID field. If not specified it defaults to -1.
      (-d|a|c|p) These are mutually exclusive options:
  -d  Drops the table, then recreates it and populates
        it with current shape file data.
  -a  Appends shape file into current table, must be
       exactly the same table schema.
  -c  Creates a new table and populates it, this is the
       default if you do not specify any options.
  -p  Prepare mode, only creates the table.
  -g  <geometry_column> Specify the name of the geometry column
       (mostly useful in append mode).
  -D  Use postgresql dump format (defaults to sql insert statements.
  -k  Keep postgresql identifiers case.
  -i  Use int4 type for all integer dbf fields.
  -I  Create a GiST index on the geometry column.
  -S  Generate simple geometries instead of MULTI geometries.
  -w  Use wkt format (for postgis-0.x support - drops M - drifts coordinates).
  -W <encoding> Specify the character encoding of Shape's
        attribute column. (default : "ASCII")
  -N <policy> Specify NULL geometries handling policy (insert,skip,abort)
  -n  Only import DBF file.
  -? Display this help screen

   C:\Program Files\PostgreSQL\8.3\bin>

-Oz-

you're not pointing it to a shapefile, you're sending it to a folder in some of those commands, the second one is missing quotes and the others appear to be missing some spaces. Also, you don't need the Areas - in front, that was to show what you're doing, the command starts with shp2pgsql.

Try this:
shp2pgsql "C\Penna_Topo\nhd\NHD_Area.shp" NHD_Area > nhd_area.sql
Dan Blomberg
Administrator - GPSFileDepot
GPS Units: Garmin Dakota 20, Garmin GPSMap 60csx, Nuvi 255W, Nuvi 250W, ForeRunner 110, Fenix 2, Tactix Bravo, Foretrex 401
See/Download My Maps!

abro

Dan
I copied and pasted the code you suggested. (shp2pgsql "C\Penna_Topo\nhd\NHD_Area.shp" NHD_Area > nhd_area.sql) Attached is the response:

C:\Program Files\PostgreSQL\8.3bin>shp2pgsql "C\Penna_Topo\nhd\NHD_Area.shp" NHD_Area > nhd_area.sql
C\Penna_Topo\nhd\NHD_Area.shp: shape (.shp) or index files (.shx) can not be opened, will just import attribute data.
C\Penna_Topo\nhd\NHD_Area.shp: dbf file (.dbf) can not be opened.

C:\Program Files\PostgreSQL\8.3\bin
Thanks Jim

-Oz-

does this file exist? C\Penna_Topo\nhd\NHD_Area.shp

According to your dos response it doesn't exist.
Dan Blomberg
Administrator - GPSFileDepot
GPS Units: Garmin Dakota 20, Garmin GPSMap 60csx, Nuvi 255W, Nuvi 250W, ForeRunner 110, Fenix 2, Tactix Bravo, Foretrex 401
See/Download My Maps!

abro

I finally converted the .shp files to .sql files using PostgresSQL 8.3. I am currently working on Step 4, loading the .sql files into the database without any luck. The following is the command and there response:
C:\Program Files\PostgreSQL\8.3\bin> psql -d postgis -h localhost -U postgres -p
-f nhd_flowline.sql
psql: warning: extra command-line argument "nhd_flowline.sql" ignored
psql: could not connect to server: Cannot assign requested address (0x00002741/1
0049)
        Is the server running on host "localhost" and accepting
        TCP/IP connections on port -f?
Also what are the colors all about when you say "Make sure the part in blue points to your actual map directory and remove the red-p if your user name does not use a password.

Thanks Jim

-Oz-

this appears to have two problems.  first; is nhd_flowline.sql located in C:\Program Files\PostgreSQL\8.3\bin ?  if not you need to make the end that currently just says nhd_flowline.sql into something like "C:\maps\state\nhd_flowline.sql"

Your user is postgres and if you followed all of my tutorial you likely don't have a password for that username.  If you didnt follow the tutorial you should probably include the -p
Dan Blomberg
Administrator - GPSFileDepot
GPS Units: Garmin Dakota 20, Garmin GPSMap 60csx, Nuvi 255W, Nuvi 250W, ForeRunner 110, Fenix 2, Tactix Bravo, Foretrex 401
See/Download My Maps!

abro

All 3 water files (nhd_area.sql, nhd_flowline.sql and nhd_waterbody.sql are located in C:\Program Files\PostgreSQL\8.3\bin directory. In addition, I followed your tutorial to the letter, I do not use –p in my commands unless I am just trying another path.
When I run the following command, this is the response: note, The (ERROR) response runs from row16 to row 265 and the (Insert 0 1) row is approximately 200 rows.  If this step is done right, should I be looking for a file named (area_sql.txt) before I can move to Processing the PostGIS Data.

C:\Program Files\PostgreSQL\8.3\bin>
C:\Program Files\PostgreSQL\8.3\bin> psql -d postgis -h localhost -U postgres -f
NHD_area.sql
BEGIN
psql:NHD_area.sql:15: NOTICE:  CREATE TABLE will create implicit sequence "nhd_area_gid_seq1" for serial column "nhd_area.gid"
psql:NHD_area.sql:15: ERROR:  relation "nhd_area" already exists
psql:NHD_area.sql:16: ERROR:  current transaction is aborted, commands igno
red until end of transaction block
psql:NHD_area.sql:265: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
Begin
Insert 0 1
Insert 0 1
Insert 0 1              This (Insert 0 1) is at least 200 rows long.
Insert 0 1
Insert 0 1
Commit
C:\Program Files\PostgreSQL\8.3\bin>

Thanks Jim

-Oz-

for some reason the table appears to already exist.  when it makes the .sql file it is supposed to add the drop table.

Go into pgAdmin III and to your table (its under the server, postgis, schemas, public, tables) and right click on each nhd table and click delete/drop.
Dan Blomberg
Administrator - GPSFileDepot
GPS Units: Garmin Dakota 20, Garmin GPSMap 60csx, Nuvi 255W, Nuvi 250W, ForeRunner 110, Fenix 2, Tactix Bravo, Foretrex 401
See/Download My Maps!

abro

I went into pgAdmin III and found my nhd table. Before I delete/dropped it, I copied the comment.


-Table: nhd_area
-- DROP TABLE nhd_area;
CREATE TABLE nhd_area
(  gid serial NOT NULL,  layer character varying(17),
comid integer,
fdate character varying(10),
resolution character varying(4),
gnis_id integer,
gnis_name character varying(31),
areasqkm double precision,
elevation double precision,
ftype character varying(16),
fcode integer,
shape_leng double precision,
shape_area double precision,
the_geom geometry,
CONSTRAINT nhd_area_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = (-1)))
WITH (OIDS=FALSE);
ALTER TABLE nhd_area OWNER TO postgres;


Jim

-Oz-

Yea, that doesn't matter.

Did it work now?
Dan Blomberg
Administrator - GPSFileDepot
GPS Units: Garmin Dakota 20, Garmin GPSMap 60csx, Nuvi 255W, Nuvi 250W, ForeRunner 110, Fenix 2, Tactix Bravo, Foretrex 401
See/Download My Maps!

abro

After I drop/delete the Postgis table, I ran the commands to process the Postgis Data and I get no file or directory found.


Jim

abro

Dan, thanks to all of your help, it is working fine. I went back to where GM exports data to shape files and started fresh.

Thanks again,
Jim