1

I'm learning PostGIS through a book "PostGIS in action".

The SQL code written in the book has a lot of issues and I've figured most of them except this one:

Copied from the book chapter:

CREATE TABLE ch03.paris_points(gid SERIAL PRIMARY KEY, osm_id integer, ar_num integer, feature_name varchar(200), feature_type varchar(50)); 

SELECT AddGeometryColumn('ch03', 'paris_points', 'geom', 32631,'POINT', 2); 

INSERT INTO ch03.paris_points(osm_id, ar_num, geom, feature_name,feature_type)

SELECT osm_id, ar_num, geom, tags->'name' As feature_name,

COALESCE(tags->'tourism', tags->'railway','other')::varchar(50) As feature_type

FROM ch03.paris_hetero

WHERE ST_GeometryType(geom) = 'ST_Point'; "

The ch03.paris_hetero contains every column needed. The query returned no error but it just did not insert, nothing has been added into the ch03.paris_points table.

After briefly examining the code, I really can't tell anything that went wrong.

0
4

As a rule of thumb when an INSERT does nothing but raise no error it's because you try to insert an empty set. As another rule of thumb if you got an empty set is to look for the WHERE clause first.

Also a little extra debuging tip for such situation. When writing INSERT based on a SELECT clause, try to execute the SELECT part alone to see if the result match your expectation, then write the INSERT clause on top.

EDIT: Removed part of the answer that got mixed up ST_GeometryType() and GeometryType() as LR1234567 pointed out.

2
  • 2
    Nope - you would think that but ST_GeometryType is not just an alias for GeometryType, it returns different answers: SELECT ST_GeometryType('POINT(1 2)::geometry'); returns ST_Point wile GeometryType returns 'POINT' – LR1234567 Oct 29 '15 at 8:51
  • 1
    Ran - as noted by MarHoff, make sure the query itself returns data. It could be something is wrong with your paris_hetero. Looks like you are using the first edition of the book instead of the second too. A lot has changed between PostGIS 1.5 and PostGIS 2+ – LR1234567 Oct 29 '15 at 8:55
1

If you run the following statement you will proably see that your table does not contain Points at all:

Select *

FROM ch03.paris_hetero

WHERE ST_GeometryType(geom) = 'ST_Point'; 

You can list the Geometrytypes that your table contains like this:

Select Distinct ST_GeometryType(geom)

FROM ch03.paris_hetero; 

I guess you missed some steps from the PostGIS-In-Action workflow of chapter3:

-- Create the hstore extension from sql file or in newer versions just

Create Extension hstore;

-- -- run planet_osm_line.sql from subfolder "raw_data_files"

-- then Region tagging and clipping data to a specific arrondissement

INSERT INTO ch03.paris_hetero(osm_id, geom, ar_num, tags) --[1 BEG] SELECT o.osm_id, ST_Intersection(o.geom, a.geom) As geom, a.ar_num, o.tags FROM (SELECT osm_id, ST_Transform(way, 32631) As geom, tags FROM planet_osm_line) AS o INNER JOIN ch03.arrondissements AS A ON (ST_Intersects(o.geom, a.geom));

-- -- run run planet_osm_point.sql from subfolder "raw_data_files"

-- -- run run planet_osm_polygon.sql from subfolder "raw_data_files"

-- repeat for planet_osm_polygon, planet_osm_point -- NOT SHOWN IN BOOK

INSERT INTO ch03.paris_hetero(osm_id, geom, ar_num, tags)         
SELECT o.osm_id, ST_Intersection(o.geom, a.geom) As geom, 
      a.ar_num, o.tags
FROM 
(SELECT osm_id, ST_Transform(way, 32631) As geom, tags FROM planet_osm_polygon) AS o 
  INNER JOIN ch03.arrondissements AS A ON (ST_Intersects(o.geom, a.geom));                                  

INSERT INTO ch03.paris_hetero(osm_id, geom, ar_num, tags)         
SELECT o.osm_id, o.geom, 
      a.ar_num, o.tags
FROM 
(SELECT osm_id, ST_Transform(way, 32631) As geom, tags FROM planet_osm_point) AS o 
  INNER JOIN ch03.arrondissements AS A ON (ST_Intersects(o.geom, a.geom));  

-- Create your table

  CREATE TABLE ch03.paris_points(gid SERIAL PRIMARY KEY, osm_id integer, ar_num integer, feature_name varchar(200), feature_type varchar(50)); 

  SELECT AddGeometryColumn('ch03', 'paris_points', 'geom', 32631,'POINT', 2); 

-- or better just

CREATE TABLE ch03.paris_points(gid SERIAL PRIMARY KEY, osm_id integer, ar_num integer, feature_name varchar(200), feature_type varchar(50),geom geometry(Point,32631)); 

-- Fill your table

INSERT INTO ch03.paris_points(osm_id, ar_num, geom, feature_name,feature_type)

SELECT osm_id, ar_num, geom, tags->'name' As feature_name,

COALESCE(tags->'tourism', tags->'railway','other')::varchar(50) As feature_type

FROM ch03.paris_hetero

WHERE ST_GeometryType(geom) = 'ST_Point'; 

-- Check if Points are inserted:

select * from ch03.paris_points;

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.