[postgis-users] Import CSV (was: Noob question with shp2pgsql)

[Margie Roswell]

I figured out that COPY is used to import a file into a table.

(Actually, even though I don't speak a word of Portuguese, a Portuguese
video did a great job of showing copying first into a temp table:
https://www.youtube.com/watch?v=CwsnPPub9v4 )

But the shp2pgsql thread yesterday got me thinking: to import a shapefile,
they've created a utility so that *we don't have to set up the structure of
the table in advance*

Is there something similar on the CSV side?

My guess is that http://www.safe.com/solutions/for-databases/postgis/
might have something, but I can't quite put my finger on it.

Details on that?

Also, I'm sure there's a fee for that. Are there any other strategies for
making the table creation more efficient, when importing a file to a table?

I suppose I could copy and paste the field names from the top row in the
original Excel spreadsheet, and then manually reformat them into a CREATE
NEW TABLE statement by adding all the field types. What strategies (like
the shp2pgsql utility?) reduce the pain of importing a text file?

Margie

--
http://FarmBillPrimer.org
http://www.BaltimoreUrbanAg.org (Please send events; This site is hungry.)
http://www.ExcellentNutrition.org
http://www.packtpub.com/drupal-5-views-recipes/book


On Fri, Apr 12, 2013 at 6:14 PM, David Rush <david at rushtone.com> wrote:

> Total noob to PostgreSQL and PostGIS here.  Trying to follow examples from
> the Obe+Hsu book (1st Ed) in using shp2pgsql from the command line to
> import some tiger county data.
>
> I ran this:
>
> shp2pgsql -s 4269 -g geom_4269 -W LATIN1
> c:/users/david/downloads/tl_2012_us_county/tl_2012_us_county.shp
> public.us_counties psql -h localhost -U postgres -p 5432 -d mygisdb
>
> Thanks to an archive of this list that led me to add the "-W LATIN1" param
> (it was failing with an error w/out it).
>
> Now the command runs for several minutes, spitting out mostly zillions of
> hex digits, with no overt errors.  Last line it spits out is "COMMIT;".
>
> But when I go into psql, I can't find the public.us_counties table that I
> thought I just added created:
>
> mygisdb=# select * from public.us_counties;
> ERROR:  relation "public.us_counties" does not exist
> LINE 1: select * from public.us_counties;
>                       ^
> mygisdb=# select table_schema, table_name,table_type from
> information_schema.tables where
> table_schema not in ('pg_catalog','information_schema');
>  table_schema |    table_name     | table_type
> --------------+-------------------+------------
>  public       | geography_columns | VIEW
>  public       | geometry_columns  | VIEW
>  public       | spatial_ref_sys   | BASE TABLE
>  ch01         | lu_franchises     | BASE TABLE
>  ch01         | fastfoods         | BASE TABLE
> (5 rows)
>
> Poking around with pgAdmin III I can't find in anywhere, either.
>
> Is the new table us_counties hiding somewhere?  Or did it quietly fail?
>  Or what?
>
> David
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130413/0744100f/attachment.html>