<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi Margie,<br><br>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.<br><br>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.<br><br>There are tools to assist with this operation, MapForce is a commercial one, others can probably found here:<br><a
href="http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools">http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools</a> <br><br>Brent Wood <br><br>--- On <b>Sun, 4/14/13, Margie Roswell <i><mroswell@gmail.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Margie Roswell <mroswell@gmail.com><br>Subject: [postgis-users] Import CSV (was: Noob question with shp2pgsql)<br>To: "PostGIS Users Discussion" <postgis-users@lists.osgeo.org><br>Date: Sunday, April 14, 2013, 12:54 AM<br><br><div id="yiv524095513">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 rel="nofollow" target="_blank"
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 rel="nofollow" target="_blank" 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 rel="nofollow" target="_blank" href="http://FarmBillPrimer.org">http://FarmBillPrimer.org</a></div><a rel="nofollow" target="_blank" href="http://www.BaltimoreUrbanAg.org">http://www.BaltimoreUrbanAg.org</a> (Please send events; This site is hungry.)<br>
<a rel="nofollow" target="_blank" href="http://www.ExcellentNutrition.org">http://www.ExcellentNutrition.org</a><br><a rel="nofollow" target="_blank" href="http://www.packtpub.com/drupal-5-views-recipes/book">http://www.packtpub.com/drupal-5-views-recipes/book</a><br>
</div>
<br><br><div class="yiv524095513gmail_quote">On Fri, Apr 12, 2013 at 6:14 PM, David Rush <span dir="ltr"><<a rel="nofollow" ymailto="mailto:david@rushtone.com" target="_blank" href="/mc/compose?to=david@rushtone.com">david@rushtone.com</a>></span> wrote:<br><blockquote class="yiv524095513gmail_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="yiv524095513HOEnZb"><font color="#888888">
<div><br></div><div>David</div></font></span></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a rel="nofollow" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="/mc/compose?to=postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a rel="nofollow" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>
</div><br>-----Inline Attachment Follows-----<br><br><div class="plainMail">_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" href="/mc/compose?to=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></div></blockquote></td></tr></table>