504808 (2) [Avatar] Offline
#1
Purchased your book last week during your talk at FOSS4G 2017 BOSTON. The postgis is all new for me. I began reading the book this morning and my excitement increased with each turn of the page until I approached pg. 23. I was following along by typing the commands in the command line and received an error:

  postgis_in_action=# INSERT INTO ch01.highways (gid, feature, name, state, geom)
  postgis_in_action-# SELECT gid, feature, name, state, ST_Transform(geom, 2163)
  postgis_in_action-# FROM ch01.highways_staging
  postgis_in_action-# WHERE feature LIKE 'Principal Highway%';
  ERROR:  relation "ch01.highways_staging" does not exist
    LINE 3: FROM ch01.highways_staging


At first glance, I assumed this may not be needed since the next sentenced that followed referred to command-line, so I tried that and received this error:

  postgis_in_action=# shp2pgsql -s 4269:2163 -g geom
  postgis_in_action-# -I /data/roadtrl020.shp ch01.highways_staging
  postgis_in_action-# | psql -h localhost -U postgres -p 5432 -d postgis_in_action
  postgis_in_action-# ;
  ERROR:  syntax error at or near "shp2pgsql"
     LINE 1: shp2pgsql -s 4269:2163 -g geom 


After awhile of re-typing and googling some of the terminology that you stated was unnecessary to know at this point I began to sink into search neverland. I downloaded your code but the command line file had a load, load and transform (exact as book). I tried all of these options and still remain stuck on pg 23. However, I proceeded to move forward to listing 1.6 and was able to run that SELECT command and return a table with 0 rows. At least I know that works, so I am close. Any feedback where I might of went wrong.

postgis_in_action=# SELECT f.franchise, COUNT(DISTINCT r.id) AS total                             
 FROM    
         ch01.restaurants AS r INNER JOIN 
         ch01.lu_franchises AS f ON r.franchise = f.id INNER JOIN                                           
         ch01.highways AS h ON ST_DWithin(r.geom, h.geom,1609)                                              
GROUP BY f.franchise                                                                               
ORDER BY total DESC;


franchise | total
-----------+-------
(0 rows)

Any feedback you provided would be greatly appreciated. Thank you in advance for sharing your time.
regina.leo (260) [Avatar] Offline
#2
At a glance it looks like you are trying to call shp2pgsql from your psql prompt.

shp2pgsql is a command-line tool, not part of psql so it has to be called directly from the OS shell (not PSQL).

We stated the ch01.highways_staging loading after the query instead of before the query. We should have put that before the query. Sorry for that.





504808 (2) [Avatar] Offline
#3
Got it. Thank you so much for the quick response. Moving forward when the book references command-line tool these should be called from the command line rather than psql, correct?

No worries about the ch01.highways_staging query. To be clear, that prompt should not stop me from producing the query on pg. 24 in reference to the "fruit of your labor"?
regina.leo (260) [Avatar] Offline
#4
Correct on first.

Second question not sure what you mean by the prompt shouldn't prevent you from moving forward.

So here are the steps you need to do

1) You load the ch01.highways_staging using shp2pgsql. Note the example shp2pgsql includes the -s 4269:2163 which does the ST_Transform part for you already. Doesn't harm to redo it though or just use -s 4269
2) After you load, then you run the query.