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

[pcreso at pcreso.com]

Hi Margie,

A shapefile is a structured set of files with self-documented column names & data types - so contains all the information required to create an equivalent table in a standard way. A CSV is just a text file, which has no typing information & may or may not have column names included. It does not necessarily contain the information describing the columns which is required to create an appropriate database table.

So importing a shapefile & csv are not that comparable. If you come up with a standard CSV format, which includes column names & typing info, you could automate the process, even try to auto-type the columns based on content, but it would only work for CSV's which conformed to your spec.

There are tools to assist with this operation, MapForce is a commercial one, others can probably found here:
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools 

Brent Wood 

--- On Sun, 4/14/13, Margie Roswell <mroswell at gmail.com> wrote:

From: Margie Roswell <mroswell at gmail.com>
Subject: [postgis-users] Import CSV (was: Noob question with shp2pgsql)
To: "PostGIS Users Discussion" <postgis-users at lists.osgeo.org>
Date: Sunday, April 14, 2013, 12:54 AM

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.orghttp://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 existLINE 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





-----Inline Attachment Follows-----

_______________________________________________
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/4acdba0f/attachment.html>