MapServer / PostGIS problem

ms-postgis.md

Overview

• works on 6.4.x
• fails on 7.0.2

Environment

MapServer 7.0.2 PostgreSQL 9.3.14 PostGIS 2.2

MapServer LAYER object snippet

LAYER
 NAME "ca-1.0-ascii"
 STATUS ON
 DEBUG 5
 CONNECTION "host=localhost dbname=foo user=foo2 password=foo3"
 CONNECTIONTYPE POSTGIS
 PROCESSING "CLOSE_CONNECTION=DEFER"
 DATA "the_geom from (select * from data_mart_ca_live order by instance_datetime desc) as subquery using unique data_payload_id using srid=4326"
 TYPE POINT
 DUMP TRUE
 HEADER "/home/tkralidi/msc-ows/msc-ows/etc/templates/query_layer_header-en.html"
 TEMPLATE "/home/tkralidi/msc-ows/msc-ows/etc/templates/query_layer_body.html"
 PROJECTION
  "init=epsg:4326"
 END

GetMap

Request

http://host/ows?VERSION=1.1.1&LAYERS=ca-1.0-ascii&FORMAT=image%2Fpng&TRANSPARENT=true&STYLES=problems&SERVICE=WMS&REQUEST=GetMap&SRS=EPSG%3A42304&BBOX=-1292142.7888301,1897873.9571224,-381319.15994704,2382178.9682508&WIDTH=1489&HEIGHT=792&exceptions=INIMAGE

Response

msPostGISLayerWhichShapes(): Query error. Error executing query. Check server logs

Log trace

[Wed Nov  9 21:19:35 2016].766747 msPostGISLayerOpen called: the_geom from (select * from data_mart_ca_live order by instance_datetime desc) as subquery using unique data_payload_id using srid=4326
[Wed Nov  9 21:19:35 2016].766997 msPostGISLayerOpen: No connection in pool, creating a fresh one.
[Wed Nov  9 21:19:35 2016].805941 msConnPoolRegister(ca-1.0-ascii,host=localhost dbname=foo user=foo2 password=foo3)
[Wed Nov  9 21:19:35 2016].917313 msPostGISLayerOpen: Got PostGIS version 20200.
[Wed Nov  9 21:19:35 2016].917408 msPostGISLayerOpen: Forcing 2D geometries: yes.
[Wed Nov  9 21:19:35 2016].917434 msPostGISLayerFreeItemInfo called.
[Wed Nov  9 21:19:35 2016].918801 msPostGISLayerInitItemInfo called.
[Wed Nov  9 21:19:35 2016].921461 msPostGISLayerWhichShapes called.
[Wed Nov  9 21:19:35 2016].921525 msPostGISParseData called.
[Wed Nov  9 21:19:35 2016].921564 msPostGISParseData: unique_column=data_payload_id, srid=4326, geom_column_name=the_geom, table_name=(select * from data_mart_ca_live order by instance_datetime desc) as subquery
[Wed Nov  9 21:19:35 2016].921600 msPostGISBuildSQL called.
[Wed Nov  9 21:19:35 2016].921623 msPostGISBuildSQLItems called.
[Wed Nov  9 21:19:35 2016].921649 msPostGISBuildSQLItems: 10 items requested.
[Wed Nov  9 21:19:35 2016].921683 msPostGISBuildSQLFrom called.
[Wed Nov  9 21:19:35 2016].921706 msPostGISBuildSQLWhere called.
[Wed Nov  9 21:19:35 2016].921727 msPostGISBuildSQLSRID called.
[Wed Nov  9 21:19:35 2016].921747 msPostGISBuildSQLSRID: SRID provided (4326)
[Wed Nov  9 21:19:35 2016].921768 msPostGISBuildSQLBox called.
[Wed Nov  9 21:19:35 2016].921918 msPostGISLayerWhichShapes query: select "stn_nam","avg_wnd_dir_10m_mt58_60","avg_wnd_spd_10m_mt58_60_symbol","air_temp","stn_pres","mslp","dwpt_temp","rel_hum","observation_qa_summary","observation_qa_problem_summary",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from data_mart_ca_live order by instance_datetime desc) as subquery where the_geom && ST_GeomFromText('POLYGON((-127.881959628204 63.7547758989546,-127.881959628204 70.5833900726923,-103.751686743445 70.5833900726923,-103.751686743445 63.7547758989546,-127.881959628204 63.7547758989546))',4326) and ()
[Wed Nov  9 21:19:35 2016].923861 msPostGISLayerWhichShapes query status: PGRES_FATAL_ERROR (7)
[Wed Nov  9 21:19:35 2016].923910 msPostGISLayerWhichShapes(): Error (ERROR:  syntax error at or near ")"
LINE 1: ...758989546,-127.881959628204 63.7547758989546))',4326) and ()
                                                                      ^
) executing query: select "stn_nam","avg_wnd_dir_10m_mt58_60","avg_wnd_spd_10m_mt58_60_symbol","air_temp","stn_pres","mslp","dwpt_temp","rel_hum","observation_qa_summary","observation_qa_problem_summary",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from data_mart_ca_live order by instance_datetime desc) as subquery where the_geom && ST_GeomFromText('POLYGON((-127.881959628204 63.7547758989546,-127.881959628204 70.5833900726923,-103.751686743445 70.5833900726923,-103.751686743445 63.7547758989546,-127.881959628204 63.7547758989546))',4326) and ()
[Wed Nov  9 21:19:35 2016].923939 msPostGISLayerWhichShapes(): Query error. Error executing query. Check server logs
[Wed Nov  9 21:19:35 2016].923965 msPostGISLayerFreeItemInfo called.
[Wed Nov  9 21:19:35 2016].924118 msPostGISLayerClose called: the_geom from (select * from data_mart_ca_live order by instance_datetime desc) as subquery using unique data_payload_id using srid=4326
[Wed Nov  9 21:19:35 2016].924143 msConnPoolRelease(ca-1.0-ascii,host=localhost dbname=foo user=foo2 password=foo3)
[Wed Nov  9 21:19:35 2016].924246 msDrawMap(): Image handling error. Failed to draw layer named 'ca-1.0-ascii'.