PostGIS query problem

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

PostGIS query problem

Fernando Martins Pimenta
Hi,

I am using the query below to return a hydrograph of a region (using POSTGIS). This query works perfectly when I use it in pgadmin. With the mapserver, the connection with the client is lost. It records a query that shows no errors in SQL, just shows that the connection has lost. Why is that?

DATA "geom FROM (
         SELECT
            hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
          FROM
            vector.hidrography AS hidro,
            vector.gcc AS gcc
          WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = 'GRANDE'
) as subquery USING UNIQUE fid USING SRID=4326"

Thanks in advance

Fernando Martins Pimenta
Graduando em Engenharia de Agrimensura e Cartográfica - UFV
Bacharel em Engenharia de Biossistemas - UFSJ

www.biosfera.dea.ufv.br




_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS query problem

Seth G-2
Hi,

What client are you using?
How long does the query take to complete?
It is likely the client stops waiting for a response.

Seth

--
web:http://geographika.co.uk
twitter: @geographika


On Thu, Jun 25, 2020, at 10:16 PM, Fernando Martins Pimenta wrote:
Hi,

I am using the query below to return a hydrograph of a region (using POSTGIS). This query works perfectly when I use it in pgadmin. With the mapserver, the connection with the client is lost. It records a query that shows no errors in SQL, just shows that the connection has lost. Why is that?

DATA "geom FROM (
         SELECT
            hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
          FROM
            vector.hidrography AS hidro,
            vector.gcc AS gcc
          WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = 'GRANDE'
) as subquery USING UNIQUE fid USING SRID=4326"

Thanks in advance

Graduando em Engenharia de Agrimensura e Cartográfica - UFV
Bacharel em Engenharia de Biossistemas - UFSJ


_______________________________________________
mapserver-users mailing list


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS query problem

jmckenna
Administrator
In reply to this post by Fernando Martins Pimenta
Hi Fernando,

I would try using the shp2img utility at the commandline, and set CONFIG
"CPL_DEBUG" "ON" in your mapfile (see
https://mapserver.org/optimization/debugging.html#step-3-turn-on-cpl-debug-optional)
and then execute:

   shp2img -m mymap.map -o ttt.png -all_debug 5

The full query should be listed there in the response, which you can
then paste into the psql commandline, and manually execute the same
query to your database.

PS. hello to my friends in Brasil!

-jeff



--
Jeff McKenna
MapServer Consulting and Training Services
co-founder of FOSS4G
http://gatewaygeo.com/


On 2020-06-25 5:16 p.m., Fernando Martins Pimenta wrote:

> Hi,
>
> I am using the query below to return a hydrograph of a region (using
> POSTGIS). This query works perfectly when I use it in pgadmin. With the
> mapserver, the connection with the client is lost. It records a query
> that shows no errors in SQL, just shows that the connection has lost.
> Why is that?
>
> DATA "geom FROM (
>           SELECT
>              hidro.fid, hidro.name <http://hidro.name>,
> ST_Intersection(gcc.geom, hidro.geom) AS geom
>            FROM
>              vector.hidrography AS hidro,
>              vector.gcc AS gcc
>            WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name
> <http://gcc.name> = 'GRANDE'
> ) as subquery USING UNIQUE fid USING SRID=4326"
>
> Thanks in advance
>
> *Fernando Martins Pimenta <http://lattes.cnpq.br/0646984654461300>*
> Graduando em Engenharia de Agrimensura e Cartográfica - UFV
> Bacharel em Engenharia de Biossistemas - UFSJ
>
> www.biosfera.dea.ufv.br <http://www.biosfera.dea.ufv.br>
>
>
>
_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS query problem

Fernando Martins Pimenta
In reply to this post by Fernando Martins Pimenta
When I run the query in pgadmin4 query tool it works correctly.
SELECT
hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
FROM
vector.hidrography AS hidro,
vector.gcc AS gcc
WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = UPPER('grande')

When I run the same query in mapfile it does not work (it returns only null).

DATA "geom FROM (
SELECT
hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
FROM
vector.hidrography AS hidro,
vector.gcc AS gcc
WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = UPPER('grande')
) as subquery USING UNIQUE fid USING SRID=4326"

The difference is "as subquery USING UNIQUE fid USING SRID=4326"

When I run shp2image the query returns:

...
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISLayerFreeItemInfo called.
msPostGISLayerClose called: geom FROM (
          SELECT
            hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
          FROM
            vector.hidrography AS hidro,
            vector.gcc AS gcc
          WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = UPPER('grande')
) as subquery USING UNIQUE fid USING SRID=4326
msConnPoolRelease(hidrografia,host=************** dbname=****** user=****** password=****** port=5432,0x55c19e119740)
msDrawMap(): Layer 0 (hidrografia), 163.260s
msDrawMap(): Drawing Label Cache, 0.000s
msDrawMap() total time: 163.260s
msSaveImage(ttt.png) total time: 0.011s
msFreeMap(): freeing map at 0x55c19e10d3f0.
freeLayer(): freeing layer at 0x55c19e114ae0.
msPostGISLayerIsOpen called.
shp2img total time: 163.272s
msConnPoolClose(host=************ dbname=******** user=******** password=******** port=5432,0x55c19e119740)
GDAL: In GDALDestroy - unloading GDAL shared library.

PostgreSQL log returns:

FATAL: connection with client has been lost
COMMAND: select ST_AsBinary (("geom"), 'NDR') as geom, "fid" :: text from (
SELECT
hydro.fid, hydro.name, ST_Intersection (gcc.geom, hydro.geom) AS geom
FROM
vector.hidrography AS hydro,
vector.gcc AS gcc
WHERE ST_Intersects (gcc.geom, hydro.geom) AND gcc.name = UPPER ('grande')) as subquery where "geom" && ST_GeomFromText ('POLYGON ((- 46.7031998460824 -15.4471601311111, -46.703199856608-9353563593463563593563561035610610356)) , -43.0600566044731 -15.4471601311111, -46.7031998460824 -15.4471601311111)) ', 4326)

I didn't find out why the mapserver is not executing this query correctly.

Fernando Martins Pimenta
Graduando em Engenharia de Agrimensura e Cartográfica - UFV
Bacharel em Engenharia de Biossistemas - UFSJ

www.biosfera.dea.ufv.br





On Fri, Jun 26, 2020 at 1:02 PM <[hidden email]> wrote:
Send mapserver-users mailing list submissions to
        [hidden email]

To subscribe or unsubscribe via the World Wide Web, visit
        https://lists.osgeo.org/mailman/listinfo/mapserver-users
or, via email, send a message with subject or body 'help' to
        [hidden email]

You can reach the person managing the list at
        [hidden email]

When replying, please edit your Subject line so it is more specific
than "Re: Contents of mapserver-users digest..."


Today's Topics:

   1. PostGIS query problem (Fernando Martins Pimenta)
   2. Re: PostGIS query problem (Seth G)
   3. Re: PostGIS query problem (Jeff McKenna)
   4. Sliver when displaying 0°-360° NetCDF data in EPSG:3857
      (Rousseau Lambert2, Louis-Philippe (EC))
   5. Problem with Python MapScript queryByRect (Just van den Broecke)


----------------------------------------------------------------------

Message: 1
Date: Thu, 25 Jun 2020 17:16:33 -0300
From: Fernando Martins Pimenta <[hidden email]>
To: [hidden email]
Subject: [mapserver-users] PostGIS query problem
Message-ID:
        <[hidden email]>
Content-Type: text/plain; charset="utf-8"

Hi,

I am using the query below to return a hydrograph of a region (using
POSTGIS). This query works perfectly when I use it in pgadmin. With the
mapserver, the connection with the client is lost. It records a query that
shows no errors in SQL, just shows that the connection has lost. Why is
that?

DATA "geom FROM (
         SELECT
            hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS
geom
          FROM
            vector.hidrography AS hidro,
            vector.gcc AS gcc
          WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = 'GRANDE'
) as subquery USING UNIQUE fid USING SRID=4326"

Thanks in advance

*Fernando Martins Pimenta <http://lattes.cnpq.br/0646984654461300>*
Graduando em Engenharia de Agrimensura e Cartográfica - UFV
Bacharel em Engenharia de Biossistemas - UFSJ

www.biosfera.dea.ufv.br
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20200625/e300a5d6/attachment-0001.html>

------------------------------

Message: 2
Date: Thu, 25 Jun 2020 22:20:50 +0200
From: "Seth G" <[hidden email]>
To: [hidden email]
Subject: Re: [mapserver-users] PostGIS query problem
Message-ID: <[hidden email]>
Content-Type: text/plain; charset="utf-8"

Hi,

What client are you using?
How long does the query take to complete?
It is likely the client stops waiting for a response.

Seth

--
web:http://geographika.co.uk
twitter: @geographika


On Thu, Jun 25, 2020, at 10:16 PM, Fernando Martins Pimenta wrote:
> Hi,
>
> I am using the query below to return a hydrograph of a region (using POSTGIS). This query works perfectly when I use it in pgadmin. With the mapserver, the connection with the client is lost. It records a query that shows no errors in SQL, just shows that the connection has lost. Why is that?
>
> DATA "geom FROM (
>  SELECT
>  hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
>  FROM
>  vector.hidrography AS hidro,
>  vector.gcc AS gcc
>  WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = 'GRANDE'
>  ) as subquery USING UNIQUE fid USING SRID=4326"
>
> Thanks in advance
>
> *Fernando Martins Pimenta <http://lattes.cnpq.br/0646984654461300>*
> Graduando em Engenharia de Agrimensura e Cartográfica - UFV
> Bacharel em Engenharia de Biossistemas - UFSJ
>
> www.biosfera.dea.ufv.br
>
> _______________________________________________
> mapserver-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20200625/af1484e5/attachment-0001.html>

------------------------------

Message: 3
Date: Thu, 25 Jun 2020 21:36:58 -0300
From: Jeff McKenna <[hidden email]>
To: [hidden email]
Subject: Re: [mapserver-users] PostGIS query problem
Message-ID:
        <[hidden email]>
Content-Type: text/plain; charset=utf-8; format=flowed

Hi Fernando,

I would try using the shp2img utility at the commandline, and set CONFIG
"CPL_DEBUG" "ON" in your mapfile (see
https://mapserver.org/optimization/debugging.html#step-3-turn-on-cpl-debug-optional)
and then execute:

   shp2img -m mymap.map -o ttt.png -all_debug 5

The full query should be listed there in the response, which you can
then paste into the psql commandline, and manually execute the same
query to your database.

PS. hello to my friends in Brasil!

-jeff



--
Jeff McKenna
MapServer Consulting and Training Services
co-founder of FOSS4G
http://gatewaygeo.com/


On 2020-06-25 5:16 p.m., Fernando Martins Pimenta wrote:
> Hi,
>
> I am using the query below to return a hydrograph of a region (using
> POSTGIS). This query works perfectly when I use it in pgadmin. With the
> mapserver, the connection with the client is lost. It records a query
> that shows no errors in SQL, just shows that the connection has lost.
> Why is that?
>
> DATA "geom FROM (
>           SELECT
>              hidro.fid, hidro.name <http://hidro.name>,
> ST_Intersection(gcc.geom, hidro.geom) AS geom
>            FROM
>              vector.hidrography AS hidro,
>              vector.gcc AS gcc
>            WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name
> <http://gcc.name> = 'GRANDE'
> ) as subquery USING UNIQUE fid USING SRID=4326"
>
> Thanks in advance
>
> *Fernando Martins Pimenta <http://lattes.cnpq.br/0646984654461300>*
> Graduando em Engenharia de Agrimensura e Cartográfica - UFV
> Bacharel em Engenharia de Biossistemas - UFSJ
>
> www.biosfera.dea.ufv.br <http://www.biosfera.dea.ufv.br>
>
>
>


------------------------------

Message: 4
Date: Fri, 26 Jun 2020 13:00:31 +0000
From: "Rousseau Lambert2, Louis-Philippe (EC)"
        <[hidden email]>
To: "[hidden email]"
        <[hidden email]>
Subject: [mapserver-users] Sliver when displaying 0°-360° NetCDF
        data in EPSG:3857
Message-ID:
        <[hidden email]>
Content-Type: text/plain; charset="iso-8859-1"

Hi,

This issue is related to:

  *   https://github.com/mapserver/mapserver/commit/722716c49de2399f54b275ab31437f0e8c92cd63
  *   https://github.com/mapserver/mapserver/commit/4c2aeb4bbd229723d8ea67c337cf02350575b2ae

The issue is that when I try to visualize (via WMS) a layer with a NetCDF data file with longitude from 0° to 360° in EPSG:3857, there is a sliver around longitude 0° where no data is displayed.

We already had that issue with the same files using EPSG:4326 and it's been fixed in MapServer (see merge request above).

Here is where you can find example files: https://dd.weather.gc.ca/model_giops/netcdf/lat_lon/2d/00/003/CMC_giops_votemper_depth_0.5_latlon0.2x0.2_3h-mean_{yyyymmdd}00_P003.nc

We are testing using: MapServer version 7.4.4 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=POINT_Z_M INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

Here is a minimal mapfile to reproduce:

    MAP
 NAME ""
 IMAGETYPE PNG
 EXTENT -180 -90 180 90
 MAXSIZE 4096
 SIZE 500 300
 IMAGECOLOR 255 255 255
 PROJECTION
  "init=epsg:4326"
 END
 TRANSPARENT ON
  DEBUG 5
     WEB
      METADATA
    "ows_extent" "-180 -90 180 90"
    "wms_getmap_formatlist" "image/png,image/jpeg"
    "wms_timeformat" "YYYY-MM-DDTHH:MM:SSZ"
    "wms_getfeatureinfo_formatlist" "text/plain"
    "ows_enable_request" "*"
    "ows_abstract" ""
    "ows_srs" "EPSG:4326 EPSG:3857"
    "ows_title" ""
      END
     END
    LAYER
     NAME "TEST"
     DEBUG 5
     TYPE RASTER
     PROCESSING "GDAL_NETCDF_BOTTOMUP=YES"
     TOLERANCE 15
     TEMPLATE "ttt.html"
PROJECTION
 "proj=longlat"
 "a=6371229"
 "b=6371229"
 "lon_wrap=180"
 "no_defs"
END
     DATA ''
     METADATA
      "ows_title" ""
      "ows_abstract" ""
      "ows_extent" "-180 -80.1 180 89.9"
      "ows_geomtype" "Geometry"
     END
CLASS
 NAME ""
 STYLE
  COLORRANGE 0 0 127 127 0 0
  DATARANGE 200.00 400
 END
END
    END
END

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20200626/09ce9091/attachment.html>

------------------------------

Message: 5
Date: Fri, 26 Jun 2020 17:55:01 +0200
From: Just van den Broecke <[hidden email]>
To: [hidden email]
Subject: [mapserver-users] Problem with Python MapScript queryByRect
Message-ID: <[hidden email]>
Content-Type: text/plain; charset=utf-8; format=flowed

Hi,

I am reviving a Python 2 MapServer MapScript app that used to work 7
years ago (and still is in old deployment) but stuck in some MapScript
calls that fail but also throw no Exceptions.

Purpose of the app "MapGlow" [1] is to generate a WMS-heatmap triggered
by specific STYLE parameters. When no heatmap STYLE is requested
requests are delegated to map_file.OWSDispatch() etc. The latter works
fine so the overall setup/stack/mapfile is working. Using MS 7.6.0 in
Docker (compose) with Lighttp, FastCGI, WSGI, flup.

It is only when  layerObj.queryByRect() is called, a HTTP 500 is
returned after a FastCGI hard error, no means to catch an Exception. The
passed MapFile and Rect objects seem ok. Also I noticed that
layerObj.metadata had 0 items. I can see that I have proper SWIG objects
like
<mapscript.layerObj; proxy of <Swig Object of type 'layerObj *' at
0x7fded06b1de0> >
If only I could get a hint, a MapFile setting, maybe some log output or
debug flag I can set? The WIP is on GH, I can point at the failing line
[2] but the code is
quite involved (mind, my first Python 7-9 years ago):

[1] https://github.com/justb4/mapglow
[2]
https://github.com/justb4/mapglow/blob/master/app1/wms/mapglow_ms.py#L249

Thanks for any help,

Just van den Broecke
The Netherlands


------------------------------

Subject: Digest Footer

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

------------------------------

End of mapserver-users Digest, Vol 149, Issue 15
************************************************

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS query problem

Carlos Ruiz
Hola, Fernando,

I don't know what's really going on but despite if this is a GDAL error or something else, I suggest you to try the following in the MAP file LAYER section:

PROCESSING "CLOSE_CONNECTION=DEFER"

Another question: does PostgreSQL server is running in another machine ?


MSc. Carlos Ruiz

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users