and thank you for your reply. Actually I got all sorted out. I succesfully loaded three sets of shapes, each 415 pcs, into three different tables. I am not sure, but I think that the order of on which you enter the options into command line matters with shp2pgsql. I had also problems with -I option. With only three tables, I did the indexing afterwards. So I am nowadays using the exact order in which they are presented in the "Using PostGIS" manual. Maybe I am a little superstitous 
?
Yep, I do understand the psql thing but ever second or third instruction says: use the command pgsql...
I appreciate your help, thanks.
Hi Mika,
Apologies for not responding sooner. I've fallen a bit behind on the list traffic and missed your questions. I don't have definitive answers for all of them, but will give tell you what I can below.
Q: Can you pass me any hint how to do this so that the first shp would
create the table and the rest would append into it? Table name could and
should be static.
A: Not sure I completely understand. You want the table to be created using the schema of the first shapefile, and then to be populated with the contents of all the subsequent shapefiles? If so, I think I would do exactly what you did and create an empty table with the correct schema, then append each of the shapefiles to it.
Q: I have also another question to anyone who knows shp2psql. Why can't I
get the -s option to work. I have tried -s 2393, -s EPSG:2393, -s
"EPSG:2393" and have been getting nothing but errors like something:
"current transaction is aborted...".
-A: I have had problems specifying the SRS at load time as well. My solution has been to update the srid field in the in the PostGIS geometry_columns table after the data is all loaded. You can do that from an SQL prompt using the "setsrid" command:
# update "table_name" set "the_geom" = setsrid ("the_geom", 4326);
Check that it works:
# select distinct SRID("table_name"."the_geom") from "table_name";
Q: Yet one stupid question. What's the difference between psql and pgsql
commands?
A: psql is an application that provides a command line interface to the PostgreSQL system. pgsql is the term used when speaking about PL/pgSQL. PL/pgSQL is a loadable procedural language for the PostgreSQL database system. Basically it refers to PostgreSQL's implementation of the ANSI SQL language - I think. It also appears to be used as an abbreviation when talking about "PostgreSQL" itself.
Cheers,
Roger
--
On Thu, Mar 27, 2008 at 3:18 PM, Lehtonen, Mika <mika@anonymised.com <mailto:mika@anonymised.com>> wrote:
Never mind Roger, got it working. I just created an empty table with a
proper structure, used -a option and replaced another $i with a
name of
that table. It seems to be working although I still can't
understand why
the -s option isn't always functioning. Anyway thanks for your
contribution, it really helped.
cheers
mika
Lehtonen, Mika kirjoitti:
> Hi Roger,
>
> thanks, it really did work, although I added a pipe and leave the
> sql-creation out because I couldn't get it working. I realize
now that
> it may have been caused by a lack of rights in that directory.
Anyway
> the pipe works. But I need more and I am not good at these.
>
> Can you pass me any hint how to do this so that the first shp would
> create the table and the rest would append into it? Table name
could and
> should be static.
> I have also another question to anyone who knows shp2psql. Why
can't I
> get the -s option to work. I have tried -s 2393, -s EPSG:2393, -s
> "EPSG:2393" and have been getting nothing but errors like something:
> "current transaction is aborted...".
>
> Yet one stupid question. What's the difference between psql and
pgsql
> commands?
>
> reg
> mika
>
> Roger.Andre@anonymised.com kirjoitti:
> >> ROTFL! Wait, if you are going to cut and paste, use this instead:
>> >> for i in `ls -1 *.shp | awk -F"." '{print $1}'`; do; shp2pgsql
-D $i $i
>> my_database > $i.sql; pgsql -d my_database -f $i.sql; done
>> --
>> Roger
>> -----Original Message-----
>> From: Lehtonen, Mika [mailto:mika@anonymised.com]
>> Sent: Wednesday, March 19, 2008 2:13 PM
>> To: Andre, Roger/SEA
>> Cc: geoserver-users
>> Subject: Re: [Geoserver-users] Batch loader option?
>>
>> Watch out!
>> I am getting pimples of that jargon. Does it really work? Looks
terrible
>> but who cares.
>> Joke, joke! I was just using too long fine GUIs' before I
jumped back to
>> this hell. Have to copy-paste it.
>>
>> Thanks a lot, I will need this. Last time I checked, there were
3300
>> shapes in my server. Should have been max. 800. =-O
>>
>> mika
>>
>> Roger.Andre@anonymised.com kirjoitti:
>> >> >>> You can batch load in PostGIS pretty easily using the
"shp2pgsql" tool
>>> >>> >> >> >>> to first create .SQL files, then using pgsql to load them. So the
>>> workflow in a Linux shell would look like this: (db_name
assumed to
>>> be "my_database" for this example)
>>>
>>> for i in `ls -1 *.shp | awk -F"." '{print $1}'` do shp2pgsql
-D $i $i
>>> my_database > $i.sql pgsql -d my_database -f $i.sql done
>>>
>>> Note that you could use "cut" instead of awk to split the filename
>>> from the ".shp".
>>> --
>>> Roger
>>>
>>> -----Original Message-----
>>> From: geoserver-users-bounces@lists.sourceforge.net
<mailto:geoserver-users-bounces@lists.sourceforge.net>
>>> [mailto:geoserver-users-bounces@lists.sourceforge.net] On
Behalf Of
>>> Lehtonen, Mika
>>> Sent: Wednesday, March 19, 2008 8:50 AM
>>> To: lmorandini@anonymised.com <mailto:lmorandini@anonymised.com>
>>> Cc: geoserver-users@lists.sourceforge.net
<mailto:geoserver-users@lists.sourceforge.net>
>>> Subject: Re: [Geoserver-users] Batch loader option?
>>>
>>> Luca,
>>>
>>> you don't happen to know how to batch load shapes into PostGIS
>>> >>> >> database.
>> >> >>> Yep, it turned out that I might not be able to use shapes,
because I
>>> need to do some filtering for the material. This is stupid. These
>>> shapes are detached out of a big database piece by piece
following the
>>> >>> >> >> >>> geographical borders. Now I am forced to attach them back into a
>>> single database, doing some filtering and detaching the
material in
>>> raster, piece by piece following the "map grid".
>>>
>>> Yeah, a consult must also earn his living. The best part is that I
>>> know PostGIS as much as a pig knows about a windmill. So, I
will be
>>> learning and getting money out of it. Why I am complaining?
>>>
>>> RESTful sounds nice.
>>>
>>> reg. mika
>>>
>>> Luca Morandini kirjoitti:
>>> >>> >>> >>>> Lehtonen, Mika wrote:
>>>> >>>> >>>> >>>> >>>>> I am not sure if you Luca were serious about building that
script,
>>>>> >>>>> >>>>> >>>>> >>>> Yes I were. Actually, I did something of the sort to load
shapefiles
>>>> into DB2, though they were all of the same extent and SRS,
which made
>>>> >>>> >> >> >>>> the task considerably easier.
>>>>
>>>> Anyway, I had in mind only a wiki page on how to do this with
Ant +
>>>> XMLTask, not a full-fledged load utility.
>>>>
>>>> >>>> >>>> >>>> >>>>> I was thinking of going through the shp-directory with Cocoon's
>>>>> directory-generator and use a xsl-template to build those
>>>>> files/dirs,
>>>>> >>>>> >>>>> >>> >>> >>> >>>>> but obviously I have to get those coord. information somehow.
>>>>> >>>>> >>>>> >>>>> >>>> As Alexandre pointed out, OGR could come to the rescue...
though I
>>>> think a more elegant way would be to use GeoTools (look at
>>>>
http://svn.geotools.org/geotools/trunk/gt/modules/plugin/shapefile/
>>>>
>>>> >>>> >>>> >>>
src/main/java/org/geotools/data/shapefile/ShapefileFileResourceInfo.ja
>>> va
>>> ).
>>> >>> >>> >>>> >>>> >>>> >>>> >>>>> P.S. This job should be done by some pro...
>>>>> >>>>> >>>>> >>>>> >>>> In other words you're not volunteering, are you ?
>>>>
>>>> Regards,
>>>>
>>>> --------------------
>>>> Luca Morandini
>>>> www.lucamorandini.it <http://www.lucamorandini.it>
>>>> --------------------
>>>>
---------------------------------------------------------------------
>>>> -
>>>> --- This SF.net email is sponsored by: Microsoft Defy all
challenges.
>>>> >>>> >> >> >>>> Microsoft(R) Visual Studio 2008.
>>>> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
>>>> _______________________________________________
>>>> Geoserver-users mailing list
>>>> Geoserver-users@lists.sourceforge.net
<mailto:Geoserver-users@lists.sourceforge.net>
>>>> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>>>> >>>> >>>> >>>> >>> >>>
>>>
----------------------------------------------------------------------
>>> --
>>> -
>>> This SF.net email is sponsored by: Microsoft Defy all challenges.
>>> Microsoft(R) Visual Studio 2008.
>>> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
>>> _______________________________________________
>>> Geoserver-users mailing list
>>> Geoserver-users@lists.sourceforge.net
<mailto:Geoserver-users@lists.sourceforge.net>
>>> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>>> >>> >>> >> >> >
>
-------------------------------------------------------------------------
> Check out the new SourceForge.net Marketplace.
> It's the best place to buy or sell services for
> just about anything Open Source.
>
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
> _______________________________________________
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
<mailto:Geoserver-users@lists.sourceforge.net>
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
--
Roger André
GIS Developer/Analyst
Enterprise Management Solutions
CH2M HILL
Tel: 425.233.3042
...
