I figured out that COPY is used to import a file into a table.<br><br>(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: <a href="https://www.youtube.com/watch?v=CwsnPPub9v4">https://www.youtube.com/watch?v=CwsnPPub9v4</a> )<br>
<br>But the shp2pgsql thread yesterday got me thinking: to import a shapefile, they've created a utility so that <i>we don't have to set up the structure of the table in advance</i><br><br>Is there something similar on the CSV side?<br>
<br>My guess is that <a href="http://www.safe.com/solutions/for-databases/postgis/">http://www.safe.com/solutions/for-databases/postgis/</a><br>might have something, but I can't quite put my finger on it.<br><br>Details on that? <br>
<br>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?<br><br>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?<br>
<br>Margie<br><br clear="all"><div><div>--</div><div><a href="http://FarmBillPrimer.org" target="_blank">http://FarmBillPrimer.org</a></div><a href="http://www.BaltimoreUrbanAg.org" target="_blank">http://www.BaltimoreUrbanAg.org</a> (Please send events; This site is hungry.)<br>
<a href="http://www.ExcellentNutrition.org" target="_blank">http://www.ExcellentNutrition.org</a><br><a href="http://www.packtpub.com/drupal-5-views-recipes/book" target="_blank">http://www.packtpub.com/drupal-5-views-recipes/book</a><br>
</div>
<br><br><div class="gmail_quote">On Fri, Apr 12, 2013 at 6:14 PM, David Rush <span dir="ltr"><<a href="mailto:david@rushtone.com" target="_blank">david@rushtone.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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.<div><br></div><div>I ran this:</div>
<div><br></div><div>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 <br></div><div>
<br></div><div>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).</div><div><br></div><div>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;".</div>
<div><br></div><div>But when I go into psql, I can't find the public.us_counties table that I thought I just added created:</div><div><br></div><div><div>mygisdb=# select * from public.us_counties;</div>
<div>ERROR: relation "public.us_counties" does not exist</div><div>LINE 1: select * from public.us_counties;</div><div> ^</div><div>mygisdb=# select table_schema, table_name,table_type from information_schema.tables where</div>
<div>table_schema not in ('pg_catalog','information_schema');</div><div> table_schema | table_name | table_type</div><div>--------------+-------------------+------------</div><div> public | geography_columns | VIEW</div>
<div> public | geometry_columns | VIEW</div><div> public | spatial_ref_sys | BASE TABLE</div><div> ch01 | lu_franchises | BASE TABLE</div><div> ch01 | fastfoods | BASE TABLE</div>
<div>(5 rows)</div><div><br></div><div>Poking around with pgAdmin III I can't find in anywhere, either.</div><div><br></div><div>Is the new table us_counties hiding somewhere? Or did it quietly fail? Or what?</div>
<span class="HOEnZb"><font color="#888888">
<div><br></div><div>David</div></font></span></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>