Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

2024-02-05 Thread Travis Kirstine via MapServer-users 
Not sure I understand the issue with the polygons.   The polygon value in
the select statement will change based on the client's view extent, for
example if the users moves the map a new request with a different polygon /
bbox would be issued to mapserver and through to postgres. The EXTENT value
defined in the mapfile defines the extent of the map or layer coverage.

I would try running EXPLAIN ANALYZE on the same query on both versions of
Postgres and see what the differences are.

explain analyze select
"sourceid"::text,"featuretyp"::text,"cartodb_id"::text,"name"::text,"sourcetype"::text,"drawingord"::text,"isnew"::text,ST_AsBinary(("geom"),'NDR')
as geom,"cartodb_id"::text from the_table_in_question where "geom" &&
ST_GeomFromText('POLYGON((-12520996.7293382
2507134.52775378,-12520996.7293382 3754586.82936786,-11273544.4277241
3754586.82936786,-11273544.4277241 2507134.52775378,-12520996.7293382
2507134.52775378))',find_srid('','iswp_sourcefeatures2022','geom')) and
("featuretyp"::text = 'polygon')






On Mon, 5 Feb 2024 at 10:35, Rob Dennett via MapServer-users <
mapserver-users@lists.osgeo.org> wrote:

> So, looking at this further, this is the bounding box for what's happening
> is that when pointed at the old db, the .map file is making the calls using
> these coordinates
>
> -12520996.7293382 2507134.52775378,-12520996.7293382
> 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241
> 2507134.52775378,-12520996.7293382 2507134.52775378
>
> and then again with these
>
> -12518550.744433 2509580.51265891,-12518550.744433
> 5004485.11588706,-10023646.1412049 5004485.11588706,-10023646.1412049
> 2509580.51265891,-12518550.744433 2509580.51265891
>
> When pointed at the new db (which does indeed have identical data and
> structure), the polygons are
>
> -11268652.4579138 2507134.52775378,-11268652.4579138
> 3754586.82936786,-10021200.1562997 3754586.82936786,-10021200.1562997
> 2507134.52775378,-11268652.4579138 2507134.52775378
>
> and
>
> -12068252.5062205 2978893.85839647,-12068252.5062205
> 4369793.96473184,-10212686.4400848 4369793.96473184,-10212686.4400848
> 2978893.85839647,-12068252.5062205 2978893.85839647
>
> I have no idea where these polygons are coming from, nor do I understand
> why they are different when pointed at the old vs. new db nor why the
> queries are called in a different order.  The .map file has a line defining
> the extent:
>
> EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357
> 4369793.96473184
>
> but that doesn't match up with these polygons.  The new polygons appear to
> be a bit smaller than the old ones, if I understand correctly.  Does anyone
> know why I am seeing what I am seeing?
>
> Thanks,
> Rob
> --
> *From:* Rahkonen Jukka 
> *Sent:* Monday, February 5, 2024 1:25 AM
> *To:* Rob Dennett ;
> mapserver-users@lists.osgeo.org 
> *Subject:* Re: [MapServer-users] Why is Mapserver slower in Postgres 13
> than in Postgres 11?
>
>
> External: Beware of links/attachments.
>
>
> Hi,
>
>
>
> See https://mapserver.org/input/vector/postgis.html, there is an example
> about how to define the SRID on the DATA line:
> DATA "the_geom from the_database using unique gid using srid=4326"
>
>
>
> The SRID value to use is the native SRID of the PostGIS table.
>
> What Mapserver does here is that with “&&” operator it selects those
> features from the table which intersect with the reference geometry and
> because of that it needs to know the SRID of the table. The reference
> geometry (POLYGON) is either the BBOX of the request or the EXTENT used in
> the mapfile, I cannot say for sure with this information. I believe that
> find_srid is rather fast but it is good to include “using srid=” anyway.
> Adding “unique” for defining the primary key of the table cannot make any
> harm either.
>
> You wrote “There are 8 select statements in each, but the statements
> aren't the same” and then you showed one of the statements. It would help
> to see also the other statement. But if the tables have the same data and
> the output from the new db is much larger, and there is no other filter in
> the SQL query than the &&, then I quess that the reference polygon of the
> latter case is bigger for some reason.
>
>
>
> -Jukka Rahkonen-
>
>
>
> *Lähettäjä:* MapServer-users  
> *Puolesta
> *Rob Dennett via MapServer-users
> *Lähetetty:* maanantai 5. helmikuuta 2024 2.54
> *Vastaanottaja:* mapserver-users@lists.osgeo.org
> *Aihe:* Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than
> in Postgres 11?
>
>
>
> I don't think so.  These queries are generated somehow.  Here's what the
> PolygonSources layer looks like, and as you can see, the query I am
> specifying is just "geom from the_table_in_question":
>
>   LAYER
>
> NAME "PolygonSources"
>
> CONNECTIONTYPE POSTGIS
>
> CONNECTION "xx"
>
> TYPE POLYGON
>
> STATUS ON
>
> DATA "geom FROM the_table_in_question"
>
> UTFITEM   "cartodb_id"
>
> UTFD

Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

2024-02-05 Thread Rob Dennett via MapServer-users 
So, looking at this further, this is the bounding box for what's happening is 
that when pointed at the old db, the .map file is making the calls using these 
coordinates

-12520996.7293382 2507134.52775378,-12520996.7293382 
3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 
2507134.52775378,-12520996.7293382 2507134.52775378

and then again with these

-12518550.744433 2509580.51265891,-12518550.744433 
5004485.11588706,-10023646.1412049 5004485.11588706,-10023646.1412049 
2509580.51265891,-12518550.744433 2509580.51265891

When pointed at the new db (which does indeed have identical data and 
structure), the polygons are

-11268652.4579138 2507134.52775378,-11268652.4579138 
3754586.82936786,-10021200.1562997 3754586.82936786,-10021200.1562997 
2507134.52775378,-11268652.4579138 2507134.52775378

and

-12068252.5062205 2978893.85839647,-12068252.5062205 
4369793.96473184,-10212686.4400848 4369793.96473184,-10212686.4400848 
2978893.85839647,-12068252.5062205 2978893.85839647

I have no idea where these polygons are coming from, nor do I understand why 
they are different when pointed at the old vs. new db nor why the queries are 
called in a different order.  The .map file has a line defining the extent:

EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357 4369793.96473184

but that doesn't match up with these polygons.  The new polygons appear to be a 
bit smaller than the old ones, if I understand correctly.  Does anyone know why 
I am seeing what I am seeing?

Thanks,
Rob

From: Rahkonen Jukka 
Sent: Monday, February 5, 2024 1:25 AM
To: Rob Dennett ; mapserver-users@lists.osgeo.org 

Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in 
Postgres 11?


External: Beware of links/attachments.



Hi,



See https://mapserver.org/input/vector/postgis.html, there is an example about 
how to define the SRID on the DATA line:
DATA "the_geom from the_database using unique gid using srid=4326"



The SRID value to use is the native SRID of the PostGIS table.

What Mapserver does here is that with “&&” operator it selects those features 
from the table which intersect with the reference geometry and because of that 
it needs to know the SRID of the table. The reference geometry (POLYGON) is 
either the BBOX of the request or the EXTENT used in the mapfile, I cannot say 
for sure with this information. I believe that find_srid is rather fast but it 
is good to include “using srid=” anyway. Adding “unique” for defining the 
primary key of the table cannot make any harm either.

You wrote “There are 8 select statements in each, but the statements aren't the 
same” and then you showed one of the statements. It would help to see also the 
other statement. But if the tables have the same data and the output from the 
new db is much larger, and there is no other filter in the SQL query than the 
&&, then I quess that the reference polygon of the latter case is bigger for 
some reason.



-Jukka Rahkonen-



Lähettäjä: MapServer-users 
mailto:mapserver-users-boun...@lists.osgeo.org>>
 Puolesta Rob Dennett via MapServer-users
Lähetetty: maanantai 5. helmikuuta 2024 2.54
Vastaanottaja: 
mapserver-users@lists.osgeo.org
Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in 
Postgres 11?



I don't think so.  These queries are generated somehow.  Here's what the 
PolygonSources layer looks like, and as you can see, the query I am specifying 
is just "geom from the_table_in_question":

  LAYER

NAME "PolygonSources"

CONNECTIONTYPE POSTGIS

CONNECTION "xx"

TYPE POLYGON

STATUS ON

DATA "geom FROM the_table_in_question"

UTFITEM   "cartodb_id"

UTFDATA   
"{\"cartodb_id\":\"[cartodb_id]\",\"sourceid\":\"[sourceid]\",\"name\":\"[name]\",\"sourcetype\":\"[sourcetype]\",\"drawingord\":\"[drawingord]\",\"featuretyp\":\"[featuretyp]\",\"isnew\":\"[isnew]\"}"

TEMPLATE WMSGetFeatureInfo

FILTERITEM "featuretyp"

CLASSITEM "sourceid"

FILTER "polygon"

CLASS

  NAME "polygon"

  EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 
AND [sourceid] != 1067)

  STYLE

COLOR "#0B3A71B3"

OUTLINECOLOR  "#AFBFD0B3"

OUTLINEWIDTH1.5

  END

END

METADATA

  "wms_title" "Polygon Source Features"

  "wms_include_items" "all"

  "wms_abstract" "Layer of all polygon geometry sources."

  "wfs_title" "Polygon Source Features"

  "wfs_srs"   "EPSG:3857 EPSG:4326"

  "wfs_enable_request" "*"

  "wfs_abstract" "Layer of all polygon geometry sources."

  "gml_include_items" "all"

  "gml_featureid" "cartodb_id"

END

PROJECTION

  "init=epsg:3857"

END

PROCESSING "CLOSE_CONNECTION=DEFER"

  END



From: James Gardner mailto:j...@internode.on.net>>
Sent: Sunday, Febru

[MapServer-users] Geos segfault error under certain conditions

2024-02-05 Thread Baev Alexandr via MapServer-users 

Hello All!
 
Under certain conditions I get a strange error:
    
The combination of spatil geometry, style of class in map and bbox in WMS 
request - leads to the libgeos segmentation error.
 
All files of Example adding to email:
* bug_json.map
* geodata_bug.geojson
* mapfile_web.py
* mapstyle_for_7_0_4.zip
 
Run this:
python3 mapfile_web.py bug_json.map
 
Passed WMS request:
http://localhost:3007/?SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&BBOX=3032662.425975159276,5637758.341410524212,3340530.939469947945,5864962.737246668898&CRS=EPSG:3857&WIDTH=1103&HEIGHT=814&LAYERS=good&FORMAT=image/png
 
Failed WMS request:
http://localhost:3007/?SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&BBOX=3032662.425975159276,5637758.341410524212,3340530.939469947945,5864962.737246668898&CRS=EPSG:3857&WIDTH=1103&HEIGHT=814&LAYERS=bad&FORMAT=image/png
 
Reproduction conditions of error:
* mapserver & python3-mapscript v7.6.4, v7.6.5 (manual package), geos v3.5.1 
(repository package) - OS linux debian 9
* mapserver & python3-mapscript v7.6.2 (repository package), geos v3.9.0 
(repository package) - OS linux debian 11
 
Error message before segafoult:
python3: geos_ts_c.cpp:3742: int 
GEOSCoordSeq_getOrdinate_r(GEOSContextHandle_t, const 
geos::geom::CoordinateSequence*, unsigned int, unsigned int, double*): 
Assertion `0 != cs' failed.
 
 
The error does not cause segfault in mapserver v7.0.4,(repository package)  
geos v3.5.1 (repository package) - OS linux debian 9:
 
Run this (mapstyle & shp files in mapstyle_for_7_0_4.zip):
python3 mapfile_web.py bug_shp.map
 
All WMS requests from the example above.
 
Error message don't segafoult - all WMS query (rendering passed):
OWSDispatch Error: msGEOSError(): GEOS library error. (message repeated 1 times)
 
 
P.S:
Are there any recommendations or patches for mapserver 7.6.*(Preferably) - what 
to do with this problem?
 
 
Baev Alexandr
old...@bk.ru

bug_json.map
Description: application/navimap


geodata_bug.geojson
Description: application/geo


mapfile_web.py
Description: application/python
<>
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users