[TinyOWS-users] Making WFS queries against hstore attribute

[Rahkonen Jukka]

-----Alkuperäinen viesti-----
Lähettäjä: tinyows-users-bounces at lists.maptools.org puolesta: Olivier Courtin
Lähetetty: ma 9.5.2011 21:47
Vastaanottaja: TinyOWS users discuss list
Aihe: Re: [TinyOWS-users] Making WFS queries against hstore attribute
 

>> What about already implemented FE PropertyIsLike operator ?
>> If hstore are also considered as SQL string, it should/could works.
>
> I am not very clever in writing filters and queries but you can try  
> PropertyIsLike with my server. It is a fresh TinyOWS rc1, and the  
> database behind is PostgreSQL 9.0 with PostGIS 1.5.2. If you want to  
> have your own database with hstore some day, it can be created from  
> OSM data with osm2pgsql by using the -k switch. Before that hstore  
> option must be added into the database by running the "hstore.sql"  
> script from the "contrib" directory.

http://188.64.1.61/cgi-bin/tinyows?service=wfs&version=1.1.0&request=getfeature&typename=tows:osm_polygon&maxfeatures=100&filter=%3Cogc:Filter%20xmlns:ogc=%22http://www.opengis.net/ogc%22%3E%3Cogc:PropertyIsLike%20wildCard=%27*%27%20singleChar=%27.%27%20escapeChar=%27!%27%3E%3Cogc:PropertyName%3Etows:tags%3C/ogc:PropertyName%3E%3Cogc:Literal%3ESaima%3C/ogc:Literal%3E%3C/ogc:PropertyIsLike%3E%3C/ogc:Filter%3E

The request looks fine, but doesn't output any feature.
Could you log the PostgreSQL request to find out why it's doesn't  
works for hstore column type ?



This is what comes into Potgresql statement log (with correct spelling 'Saimaa')

2011-05-10 00:08:22 EEST LOG:  statement: set client_encoding to 'UTF8'
2011-05-10 00:08:22 EEST LOG:  statement: SELECT substr(postgis_full_version(), 10, 5)
2011-05-10 00:08:22 EEST LOG:  statement: SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns
2011-05-10 00:08:22 EEST LOG:  statement: SELECT DISTINCT f_table_schema, f_table_name FROM geography_columns
2011-05-10 00:08:22 EEST LOG:  statement: SELECT srid, f_geometry_column FROM geometry_columns WHERE f_table_schema='public' AND f_table_name='osm_point'
2011-05-10 00:08:22 EEST LOG:  statement: SELECT * FROM spatial_ref_sys WHERE srid=3067 AND proj4text like '%%units=m%%'
2011-05-10 00:08:22 EEST LOG:  statement: SELECT a.attname AS field, t.typname AS type FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = 'public' AND c.relname = 'osm_point' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
2011-05-10 00:08:22 EEST LOG:  statement: SELECT type from geometry_columns where f_table_schema='public' and f_table_name='osm_point' and f_geometry_column='way';
2011-05-10 00:08:22 EEST LOG:  statement: SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = 'public' AND c.relname = 'osm_point' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND a.attnotnull = 't' AND a.atthasdef='f'
2011-05-10 00:08:22 EEST LOG:  statement: SELECT c.column_name FROM information_schema.constraint_column_usage c, pg_namespace n WHERE n.nspname = 'public' AND c.table_name = 'osm_point' AND c.constraint_name = (SELECT c.conname FROM pg_class r, pg_constraint c, pg_namespace n WHERE r.oid = c.conrelid AND relname = 'osm_point' AND r.relnamespace = n.oid AND n.nspname = 'public' AND c.contype = 'p')
2011-05-10 00:08:23 EEST LOG:  statement: SELECT srid, f_geometry_column FROM geometry_columns WHERE f_table_schema='public' AND f_table_name='osm_line'
2011-05-10 00:08:23 EEST LOG:  statement: SELECT * FROM spatial_ref_sys WHERE srid=3067 AND proj4text like '%%units=m%%'
2011-05-10 00:08:23 EEST LOG:  statement: SELECT a.attname AS field, t.typname AS type FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = 'public' AND c.relname = 'osm_line' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
2011-05-10 00:08:23 EEST LOG:  statement: SELECT type from geometry_columns where f_table_schema='public' and f_table_name='osm_line' and f_geometry_column='way';
2011-05-10 00:08:23 EEST LOG:  statement: SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = 'public' AND c.relname = 'osm_line' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND a.attnotnull = 't' AND a.atthasdef='f'
2011-05-10 00:08:23 EEST LOG:  statement: SELECT c.column_name FROM information_schema.constraint_column_usage c, pg_namespace n WHERE n.nspname = 'public' AND c.table_name = 'osm_line' AND c.constraint_name = (SELECT c.conname FROM pg_class r, pg_constraint c, pg_namespace n WHERE r.oid = c.conrelid AND relname = 'osm_line' AND r.relnamespace = n.oid AND n.nspname = 'public' AND c.contype = 'p')
2011-05-10 00:08:23 EEST LOG:  statement: SELECT srid, f_geometry_column FROM geometry_columns WHERE f_table_schema='public' AND f_table_name='osm_polygon'
2011-05-10 00:08:23 EEST LOG:  statement: SELECT * FROM spatial_ref_sys WHERE srid=3067 AND proj4text like '%%units=m%%'
2011-05-10 00:08:23 EEST LOG:  statement: SELECT a.attname AS field, t.typname AS type FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = 'public' AND c.relname = 'osm_polygon' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
2011-05-10 00:08:23 EEST LOG:  statement: SELECT type from geometry_columns where f_table_schema='public' and f_table_name='osm_polygon' and f_geometry_column='way';
2011-05-10 00:08:23 EEST LOG:  statement: SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = 'public' AND c.relname = 'osm_polygon' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND a.attnotnull = 't' AND a.atthasdef='f'
2011-05-10 00:08:23 EEST LOG:  statement: SELECT c.column_name FROM information_schema.constraint_column_usage c, pg_namespace n WHERE n.nspname = 'public' AND c.table_name = 'osm_polygon' AND c.constraint_name = (SELECT c.conname FROM pg_class r, pg_constraint c, pg_namespace n WHERE r.oid = c.conrelid AND relname = 'osm_polygon' AND r.relnamespace = n.oid AND n.nspname = 'public' AND c.contype = 'p')
2011-05-10 00:08:23 EEST LOG:  statement: SELECT auth_name, auth_srid, position('+units=m ' in proj4text) FROM spatial_ref_sys WHERE srid = '3067'
2011-05-10 00:08:23 EEST LOG:  statement: SELECT ST_xmin(g.extent), ST_ymin(g.extent), ST_xmax(g.extent), ST_ymax(g.extent) FROM (SELECT ST_Extent(foo.the_geom) as extent FROM (  (SELECT "way"::geometry AS "the_geom" FROM public."osm_polygon"  WHERE  CAST("tags" AS varchar) LIKE E'Saimaa' LIMIT 100) ) AS foo) AS g
2011-05-10 00:08:24 EEST LOG:  statement: SELECT auth_name, auth_srid, position('+units=m ' in proj4text) FROM spatial_ref_sys WHERE srid = '3067'
2011-05-10 00:08:24 EEST LOG:  statement: SELECT "osm_id","access","addr_flats","addr_housenumber","addr_interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","bridge","boundary","building","construction","cutting","disused","embankment","foot","highway","historic","horse","junction","landuse","layer","learning","leisure","lock","man_made","military","motorcar","name","natural","oneway","operator","power","power_source","place","railway","ref","religion","residence","route","service","shop","sport","tourism","tracktype","tunnel","waterway","width","wood","z_order","way_area","tags",ST_AsGML(3, ST_Transform("way"::geometry,3067),0, 3) AS "way"  FROM public."osm_polygon" WHERE  CAST("tags" AS varchar) LIKE E'Saimaa' LIMIT 100


The final WHERE does not return any lines from psql either.  I tried first the output as
# select cast ("tags" as varchar) from osm_polygon limit 2;
                             tags
--------------------------------------------------------------
 "z_order"=>"0", "building"=>"yes", "way_area"=>"4541.749900"
 "z_order"=>"0", "building"=>"yes", "way_area"=>"4179.683150"
(2 rows)

Simple where does not find anything
# select osm_id from  osm_polygon where cast ("tags" as varchar) like E'Saimaa';
 osm_id
--------
(0 rows)

The same query edited to have ("name" as varchar) finds 67 rows. My conclusion is that hstore behaves somehow differently but I do not know how it should be queried.

-Jukka Rahkonen-