<html>
<head>
<!-- Template generated by Exclaimer Signature Manager Exchange Edition on 01:19:38 Wednesday, 13 February 2019 -->
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css">P.ImprintUniqueID {
MARGIN: 0cm 0cm 0pt
}
LI.ImprintUniqueID {
MARGIN: 0cm 0cm 0pt
}
DIV.ImprintUniqueID {
MARGIN: 0cm 0cm 0pt
}
TABLE.ImprintUniqueIDTable {
MARGIN: 0cm 0cm 0pt
}
DIV.Section1 {
page: Section1
}
.ExternalClass * {
LINE-HEIGHT: 100%
}
</style>
</head>
<body>
<p class="ImprintUniqueID"></p>
<div>Thanks guys,<br>
<br>
I have solved this problem (but am working through another :-)<br>
<br>
<br>
The logged SQL with the error says: "... find_srid('','rdb.species_master','point'))"... and it can't find the srid. This is because my query gets the point from the maps.null_point table, not the rdb.species_master one, so there is no geometry_columns entry
for the column in the rdb table.<br>
<br>
It seems mapserver uses the first table in the SQL from clause to find the SRID for the geometry column (even if that column in not in the first table), and my query had it as the second table listed... if I change the order of the tables in the from clause
so the first one listed contains the geometry it seems to work...<br>
<br>
Not sure if this is a bug? I can't find it mentioned anywhere on Google...<br>
<br>
<br>
Cheers<br>
<br>
Brent Wood<br>
<br>
Programme leader: Environmental Information Delivery<br>
NIWA<br>
DDI: +64 (4) 3860529<br>
<br>
</div>
<br>
<br>
<table class="ImprintUniqueIDTable" style="HEIGHT: 89px; TABLE-LAYOUT: fixed; WIDTH: 100%" cellspacing="0" cellpadding="0" align="center" border="0">
<tbody>
<tr>
<td style="TABLE-LAYOUT: fixed; WIDTH: 180px"><a href="http://www.niwa.co.nz" target=""><img width="180" height="88" style="border: 0px Solid ; " src="cid:imagec1f72c.PNG@56107fed.4c9e4abf"></a><br>
<br>
</td>
<td style="TABLE-LAYOUT: fixed; WIDTH: 1159px">
<table cellpadding="0" cellspacing="0" class="731eaaaa-cda7-46c5-b7ae-55dd10b18511Table">
<tbody>
<tr>
<td nowrap="1"><font style="font-family:Calibri;font-size:10pt;color:#000000;font-weight:bold;">Brent Wood</font></td>
</tr>
<tr>
<td nowrap="1"><font style="font-family:Calibri Light;font-size:9pt;color:#000000;font-weight:bold;">Principal Technician - GIS and Spatial Data Management</font></td>
</tr>
<tr>
<td nowrap="1"><font style="font-family:Calibri Light;font-size:9pt;color:#000000;font-weight:bold;">Programme Leader - Environmental Information Delivery</font></td>
</tr>
<tr>
<td nowrap="1">
<table cellspacing="0" cellpadding="0" class="731eaaaa-cda7-46c5-b7ae-55dd10b18511Table" style="font-family:Calibri; font-size:9pt;">
<tbody>
<tr>
<td nowrap="1"><font style="font-family:Calibri;font-size:9pt;"><font style="font-family:Calibri;font-size:9pt;"><font style="font-family:Calibri;font-size:9pt;color:#244061;font-weight:bold;">T
</font><font style="font-family:Calibri;font-size:9pt;">+64-4-386-0529</font></font></font></td>
<td nowrap="1" align="Center"><font style="font-family:Calibri;font-size:9pt;"><font style="font-family:Calibri;font-size:9pt;"><font style="font-family:Calibri;font-size:9pt;"> </font></font></font></td>
<td nowrap="1"><font style="font-family:Calibri;font-size:9pt;"><font style="font-family:Calibri;font-size:9pt;"></font></font></td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td nowrap="1"><font style="font-family:Calibri;font-size:9pt;color:#000000;">National Institute of Water & Atmospheric Research Ltd (NIWA)</font></td>
</tr>
<tr>
<td nowrap="1"><font style="font-family:Calibri;font-size:9pt;color:#000000;">301 Evans Bay Parade, Greta Point, Wellington</font></td>
</tr>
<tr>
<td nowrap="1"><font style="font-family:Calibri;font-size:9pt;color:#000000;font-weight:bold;">Connect with NIWA:
</font><span style="font-family:Calibri;font-size:9pt;"><a href="https://www.niwa.co.nz" title="" target="" style="font-family:Calibri;font-size:9pt;"><span style="font-family:Calibri; font-size:9pt;">niwa.co.nz</span></a></span><font style="font-family:calibri;font-size:9pt;"> </font><span style="font-family:calibri;font-size:9pt;"><a href="https://www.facebook.com/nzniwa" title="" target="" style="font-family:calibri;font-size:9pt;"><span style="font-family:calibri; font-size:9pt;">Facebook</span></a></span><font style="font-family:Calibri;font-size:9pt;"> </font><span style="font-family:Calibri;font-size:9pt;"><a href="https://twitter.com/niwa_nz" title="" target="" style="font-family:Calibri;font-size:9pt;"><span style="font-family:Calibri; font-size:9pt;">Twitter</span></a></span><font style="font-family:Calibri;font-size:9pt;"> </font><span style="font-family:Calibri;font-size:9pt;"><a href="https://www.linkedin.com/company/niwa" title="" target="" style="font-family:Calibri;font-size:9pt;"><span style="font-family:Calibri; font-size:9pt;">LinkedIn</span></a></span><font style="font-family:Calibri;font-size:9pt;"> </font><span style="font-family:Calibri;font-size:9pt;"><a href="https://www.instagram.com/niwa_science" title="" target="" style="font-family:Calibri;font-size:9pt;"><span style="font-family:Calibri; font-size:9pt;">Instagram</span></a></span></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<table class="ImprintUniqueIDTable" style="HEIGHT: 37px; VERTICAL-ALIGN: bottom; BORDER-COLLAPSE: collapse; TABLE-LAYOUT: auto; WIDTH: 933px" cellspacing="0" cellpadding="0" border="0">
<tbody>
</tbody>
</table>
<table class="ImprintUniqueIDTable" style="HEIGHT: 14px; VERTICAL-ALIGN: bottom; BORDER-COLLAPSE: collapse; TABLE-LAYOUT: auto; WIDTH: 933px" cellspacing="0" cellpadding="0" border="0">
<tbody>
</tbody>
</table>
<table class="ImprintUniqueIDTable" style="BORDER-COLLAPSE: collapse; WIDTH: 100%" cellspacing="0" cellpadding="0" border="0">
<tbody>
</tbody>
</table>
<table class="ImprintUniqueIDTable" style="FONT-SIZE: x-small; FONT-FAMILY: Calibri; TABLE-LAYOUT: fixed" cellspacing="0" cellpadding="0" border="0">
<tbody>
<tr>
<td><font color="#244061" size="1" face="Calibri">
<table class="731eaaaa-cda7-46c5-b7ae-55dd10b18511Table">
<tbody>
<tr>
<td><font style="font-family:Calibri;font-size:7pt;color:#244061;"><font style="font-family:Calibri;font-size:8pt;color:#244061;">To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing
monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.</font></font></td>
</tr>
</tbody>
</table>
</font></td>
</tr>
</tbody>
</table>
<br>
<table class="ImprintUniqueIDTable" style="BORDER-COLLAPSE: collapse" cellspacing="0" cellpadding="0" border="0">
<tbody>
</tbody>
</table>
<table class="ImprintUniqueIDTable" style="BORDER-COLLAPSE: collapse" cellspacing="0" cellpadding="0" width="500" border="0">
<tbody>
</tbody>
</table>
<div>________________________________________<br>
From: mapserver-users <mapserver-users-bounces@lists.osgeo.org> on behalf of Lime, Steve D (MNIT) <steve.lime@state.mn.us><br>
Sent: Wednesday, February 13, 2019 08:39<br>
To: Stephen Woodbridge; mapserver-users@lists.osgeo.org<br>
Subject: Re: [mapserver-users] Help with failing Postgis query...<br>
<br>
Also, I'd turn on debugging. The PostGIS code is very good about writing meaningful debugging information to that log file.<br>
<br>
-----Original Message-----<br>
From: mapserver-users [mailto:mapserver-users-bounces@lists.osgeo.org] On Behalf Of Stephen Woodbridge<br>
Sent: Sunday, February 10, 2019 11:04 PM<br>
To: mapserver-users@lists.osgeo.org<br>
Subject: Re: [mapserver-users] Help with failing Postgis query...<br>
<br>
Brent,<br>
<br>
Are you wrapping your query like:<br>
<br>
DATA "select * from (select ROW_NUMBER() OVER (order by code) as id,<br>
code, sci_name, com_name, lower(family_com)||' ('|| family_sci||')' as<br>
Family, point from rdb.species_master s, maps.null_point p where code in<br>
(select distinct species from trawl.t_catch) limit 10) as foo using<br>
unique id using srid=4326"<br>
<br>
where srid is appropriate for your data?<br>
<br>
-Steve W<br>
<br>
On 2/10/2019 7:51 PM, Brent Wood wrote:<br>
><br>
> Hi,<br>
><br>
><br>
> I'm getting an error with an SQL in a mapfile.<br>
><br>
><br>
> The query works from from the command line:<br>
><br>
><br>
> select ROW_NUMBER() OVER (order by code) as id, code, sci_name,<br>
> com_name, lower(family_com)||' ('|| family_sci||')' as Family, point<br>
> from rdb.species_master s, maps.null_point p where code in (select<br>
> distinct species from trawl.t_catch) limit 10 ;<br>
><br>
><br>
> id,code,sci_name,com_name,family,locn<br>
> 1,AAT,Alainopasiphaea australis,Alainopasiphaea<br>
> australis,,0101000020E610000000000000000000000000000000000000<br>
><br>
> ...<br>
><br>
><br>
> When embedded in a mapfile & invoked from a browser or QGIS I get an<br>
> error:<br>
><br>
><br>
> WFS server error. ms_error->code not found<br>
> msPostGISLayerWhichShapes(): Query error. Error executing query.<br>
><br>
><br>
><br>
> Any suggestions?<br>
><br>
><br>
> Brent Wood<br>
><br>
> Programme leader: Environmental Information Delivery<br>
> NIWA<br>
> DDI: +64 (4) 3860529<br>
><br>
><br>
> <https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.niwa.co.nz&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=muvSp2QB5z9I7aenwMwa%2F5DmbW5M7wXVD%2BabvBeIS8A%3D&reserved=0><br>
><br>
><br>
> Brent Wood<br>
> Principal Technician - GIS and Spatial Data Management<br>
> Programme Leader - Environmental Information Delivery<br>
> T +64-4-386-0529<br>
><br>
> National Institute of Water & Atmospheric Research Ltd (NIWA)<br>
> 301 Evans Bay Parade, Greta Point, Wellington<br>
> Connect with NIWA: niwa.co.nz <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.niwa.co.nz&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=s2gvY68GZi7OqTFz7YYZonbbJCmdBE9zVxlf%2FizJKZY%3D&reserved=0>Facebook<br>
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2Fnzniwa&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=Euxh5tuy5xI7CaWR2wN3x3sD66w2VEP719AvMvrIm6E%3D&reserved=0>Twitter<br>
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fniwa_nz&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=xspFw9heBoCTLqKKprbTixXKOeGDtoKz5LKNZgdodUk%3D&reserved=0>LinkedIn<br>
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fniwa&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=Z1mta8714GDjOEK3%2FMwaBIAxvGL%2BhWRdrFENLx6CABg%3D&reserved=0>Instagram<br>
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fniwa_science&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&sdata=UxEdLhf2iDgWA0fQQryiFrMExBiBuWPvhwUBVQJ49C4%3D&reserved=0><br>
><br>
> To ensure compliance with legal requirements and to maintain cyber<br>
> security standards, NIWA's IT systems are subject to ongoing<br>
> monitoring, activity logging and auditing. This monitoring and<br>
> auditing service may be provided by third parties. Such third parties<br>
> can access information transmitted to, processed by and stored on<br>
> NIWA's IT systems.<br>
><br>
><br>
><br>
><br>
><br>
><br>
> _______________________________________________<br>
> mapserver-users mailing list<br>
> mapserver-users@lists.osgeo.org<br>
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fmapserver-users&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&sdata=Or5QlT2y7YnEoea9XqkSlgoyC1OAm0Q0BiklpHtZkZc%3D&reserved=0<br>
<br>
<br>
---<br>
This email has been checked for viruses by Avast antivirus software.<br>
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.avast.com%2Fantivirus&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&sdata=ENQHGV2XiSeAnJaQv8ReCiQ45Z%2B4BtZIdNqI9zbokB0%3D&reserved=0<br>
<br>
_______________________________________________<br>
mapserver-users mailing list<br>
mapserver-users@lists.osgeo.org<br>
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fmapserver-users&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&sdata=Or5QlT2y7YnEoea9XqkSlgoyC1OAm0Q0BiklpHtZkZc%3D&reserved=0<br>
_______________________________________________<br>
mapserver-users mailing list<br>
mapserver-users@lists.osgeo.org<br>
https://lists.osgeo.org/mailman/listinfo/mapserver-users<br>
</div>
<br>
<br>
<br>
<p></p>
</body>
</html>