[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] loading many similar shapefiles
From:       Martin Davis <mbdavis () refractions ! net>
Date:       2009-11-24 17:31:48
Message-ID: 4B0C1884.1080608 () refractions ! net
[Download RAW message or body]

You can easily do this with JEQL.

http://tsusiatsoftware.net/jeql/main.html

A script to read a shapefile and load it into PostGIS would look like 
the following.  You can choose whatever columns you want to load by 
giving their names.

====================================================
ShapefileReader tbl file: "data.shp";

cities = SELECT CITY_ID, NAME, MAPLABEL, SHADE, Shape_area, Shape_len, 
Geom.toWKT(GEOMETRY) AS geom FROM tbl;

DbWriter cities
    class: "org.postgresql.Driver"
    url: "jdbc:postgresql://host:1111/db-instance"
    user: "user"
    password: "pwd"
    sql: $"INSERT INTO Cities ( 
CITY_ID,NAME,MAPLABEL,SHADE,Shape_area,Shape_len,geom ) VALUES 
(?,?,?,?,?,?,ST_GeometryFromText(?,2927))"
    batchSize: 1000
    commitSize: 1000;
====================================================

P Kishor wrote:
> I have many shapefiles that are conceptually identical in structure,
> except a couple of columns in each file are named differently. These
> columns are unimportant.
> 
> How can I load all these files in the same table without the offending columns?
> 
> # generate the create table and insert commands
> shp2pgsql -s 4269 -c file1 table > load.sql
> 
> # generate the insert commands for subsequent files
> shp2pgsql -s 4269 -a file2 table >> load.sql
> shp2pgsql -s 4269 -a file3 table >> load.sql
> ..
> 
> The above commands generate INSERT statements like so --
> 
> INSERT INTO "table" (
> "area",
> "perimeter",
> "file1_",
> "file1_i",
> the_geom
> ) VALUES (
> '2.29080994500086E-04',
> '6.34477469815224E-02',
> '0106000020AD100000010000000103000020AD100000010000000D000000A4552DE928EA55C0C6F99B5 \
> 088443E404582A966D6E955C050C8CEDBD8443E40187D0569C6E955C02B508BC1C3443E403DEFC682C2E \
> 955C0BB4560AC6F443E40EB8D5A61FAE955C0A6D24F38BB413E402907B30930EA55C0503750E09D403E4 \
> 0C28A53AD85EA55C0A8716F7EC3403E403D997FF44DEB55C058FFE7305F423E402C4833164DEB55C00F4 \
> 240BE84423E40DAAD65321CEB55C0AFCF9CF529433E40D48041D2A7EA55C046274BADF7433E40FB58C16F43EA55C045F295404A443E40A4552DE928EA55C0C6F99B5088443E40'
>  );
> 
> 
> I don't want file1_ and file1_i and their corresponding values. Is
> there a way to specify which columns to load?
> 
> 
> 
> 

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]