Re: [mapserver-users] 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
mapserver-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query problem

[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.447160131, -46.703199856608-9353563593463563593563561035610610356))
, -43.0600566044731 -15.447160131, -46.7031998460824
-15.447160131)) ', 4326)

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

*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





On Fri, Jun 26, 2020 at 1:02 PM 
wrote:

> Send mapserver-users mailing list submissions to
> mapserver-users@lists.osgeo.org
>
> 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
> mapserver-users-requ...@lists.osgeo.org
>
> You can reach the person managing the list at
> mapserver-users-ow...@lists.osgeo.org
>
> 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 
> To: mapserver-users@lists.osgeo.org
> Subject: [mapserver-users] PostGIS query problem
> Message-ID:
> <
> ca+vvbcez5tuetuxjac-dst3nmlo_zctx2op4yiied0vurx-...@mail.gmail.com>
> 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
>  

Re: [mapserver-users] PostGIS query problem

[Jeff McKenna]

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 , 
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
mapserver-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query problem

[Seth G]

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 *
> Graduando em Engenharia de Agrimensura e Cartográfica - UFV
> Bacharel em Engenharia de Biossistemas - UFSJ
> 
> www.biosfera.dea.ufv.br
> 
> ___
> mapserver-users mailing list
> mapserver-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] 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
mapserver-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users