[mapserver-users] drawQuery() takes forever...
Hi,
I have sent this to postgis-group, but forum is not maybe corrent one.
Sorry for crossposting...
--
Pena
Aihe: [postgis-users] drawQuery() takes forever...
Lähetetty: 24.7.2012 17:36
Lähettäjä: Pena Kupen [ku...@wippies.fi]
Hello,
I have a postgres query, which takes about 0,1sek and resultset is one line On
my page, there is a link, which uses same sql tuo print koordinates to map.
When plotting to the maps, it's never ends.
I killed query from active task and pick sql from postgres log. It seems like
following:
select encode(AsBinary(force_collection(force_2d("koord")),'NDR'),'hex') as geom,"oid" from
(
select ... [subpressed query]
) as foobar
where koord && GeomFromText('POLYGON((3517739 7581169,3517739 7587169,3523739 7587169,3523739 7581169,3517739 7581169))',find_srid('','location','koord'))
When trying same with phpPgAdmin, first just my sql from same sql-sentence.
select ... [subpressed query]
It takes same 0,1sek and one line as result.
When trying same query as whole
select encode(AsBinary(force_collection(force_2d("koord")),'NDR'),'hex') as geom,"oid" from
(
select ... [subpressed query]
) as foobar
where koord && GeomFromText('POLYGON((3517739 7581169,3517739 7587169,3523739 7587169,3523739 7581169,3517739 7581169))',find_srid('','location','koord'))
It takes long time. After 15minutes, I killed query again.
Any idea, why this hungs?
Am I doing something wrongly?
How to trace this?
--
Pena
--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa
ja liity Wippiesiin heti!
http://www.wippies.com/
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] Using unique
Sorry doubleposting! There where little "copy/paste-error". You can ignore prior... -Pena Hello all, I'm just upgrading our system from mapserver 5.4.2 to 5.6.6. In some of our layer queries, I got now error: msPostGISParseData(): Query error. Error parsing PostGIS DATA variable. You must specifiy 'using unique' when supplying a subselect in the data definition. When using 5.4.2, this is not happaning and maps are working ok. On mapserver/postgis manual (http://mapserver.org/input/vector/postgis.html) it says: The DATA parameter is used to specify the data used to draw the map. The form of DATA is [geometry_column] from [table_name|sql_subquery] using unique [unique_key] using srid=[spatial_reference_id]. The using unique and using srid= clauses are optional, but using them improves performance. Is this a bug or why I cannot anymore use queries without "using unique"? -Pena -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] Using unique
Hello all, I'm just upgrading our system from mapserver 5.4.2 to 5.6.6. In some of our layer queries, I got now error: msPostGISParseData(http://mapserver.org/input/vector/postgis.html): Query error. Error parsing PostGIS DATA variable. You must specifiy 'using unique' when supplying a subselect in the data definition. When using 5.4.2, this is not happaning and maps are working ok. On mapserver/postgis manual ()it says: The DATA parameter is used to specify the data used to draw the map. The form of DATA is [geometry_column] from [table_name|sql_subquery] using unique [unique_key] using srid=[spatial_reference_id]. The using unique and using srid= clauses are optional, but using them improves performance. Is this a bug or why I cannot anymore use queries without "using unique"? -Pena -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Using unique
Hello Carlos and all, I'm using query, which is little cryptic. location from ( select h.location, h.oid, k.expla, h.date1, h.expla, h.total, ku.locationname as name from (select pa.location, ha.type_id as oid, ha.date1, pa_n.name, pa_n.location_id, ha.car_id, ha.total from pa join ha on ha.ha_pa_id = pa.pa_id left join pa_n on pa.pa_id = pa_n.pa_id where ha.type_id = 121557 and status='11' and ha.is_valid is NULL ) as h left join ku on h.ku_id=ku.ku_id left join k on h.type_id = k.type_id where k.code='sc' and syno=false and (h.ku_id = 'AAC') ) as foobar This has been used without any problem. -Pena Carlos Ruiz [boolean10...@yahoo.com] kirjoitti: Pena, Can you post your query ? You can use: DATA "the_geom FROM "in the traditional way Or, if you're using a query or a view datasource, you can use: DATA "the_geom FROM | AS USING UNIQUE USING SRID= Cheers from México IC Carlos Ruiz From: Pena Kupen To: mapserver-users@lists.osgeo.org Sent: Thursday, July 14, 2011 2:59 AM Subject: [mapserver-users] Using unique Sorry doubleposting! There where little "copy/paste-error". You can ignore prior... -Pena Hello all, I'm just upgrading our system from mapserver 5.4.2 to 5.6.6. In some of our layer queries, I got now error: msPostGISParseData(): Query error. Error parsing PostGIS DATA variable. You must specifiy 'using unique' when supplying a subselect in the data definition. When using 5.4.2, this is not happaning and maps are working ok. On mapserver/postgis manual (http://mapserver.org/input/vector/postgis.html) it says: The DATA parameter is used to specify the data used to draw the map. The form of DATA is [geometry_column] from [table_name|sql_subquery] using unique [unique_key] using srid=[spatial_reference_id] . The using unique and using srid= clauses are optional, but using them improves performance. Is this a bug or why I cannot anymore use queries without "using unique"? -Pena -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Using unique
Hi again, I'm answering to my by myself :-) When I add to the end of my query "using unique" as: " as foobar using unique oid " then query is again running ok. I also tried it with older version of Mapserver (5.4.2). Both versions are working ok with it. After some debugging of version 5.6.6, it seems to me , that in query, there is missing last right parenthesis if "using unique" is omitted. Some bug? -Pena Pena Kupen [ku...@wippies.fi] kirjoitti: Hello Carlos and all, I'm using query, which is little cryptic. location from ( select h.location, h.oid, k.expla, h.date1, h.expla, h.total, ku.locationname as name from (select pa.location, ha.type_id as oid, ha.date1, pa_n.name, pa_n.location_id, ha.car_id, ha.total from pa join ha on ha.ha_pa_id = pa.pa_id left join pa_n on pa.pa_id = pa_n.pa_id where ha.type_id = 121557 and status='11' and ha.is_valid is NULL ) as h left join ku on h.ku_id=ku.ku_id left join k on h.type_id = k.type_id where k.code='sc' and syno=false and (h.ku_id = 'AAC') ) as foobar This has been used without any problem. -Pena Carlos Ruiz [boolean10...@yahoo.com] kirjoitti: > Pena, > > Can you post your query ? > > You can use: > > DATA "the_geom FROM "in the traditional way > > > Or, if you're using a query or a view datasource, you can use: > > DATA "the_geom FROM | AS USING UNIQUE USING SRID= > > Cheers from México > > > IC Carlos Ruiz > > > > > From: Pena Kupen > To: mapserver-users@lists.osgeo.org > Sent: Thursday, July 14, 2011 2:59 AM > Subject: [mapserver-users] Using unique > > Sorry doubleposting! There where little "copy/paste-error". You can ignore prior... > > -Pena > > > Hello all, > > I'm just upgrading our system from mapserver 5.4.2 to 5.6.6. > In some of our layer queries, I got now error: > msPostGISParseData(): Query error. Error parsing PostGIS DATA variable. You must specifiy 'using unique' when supplying a subselect in the data definition. > When using 5.4.2, this is not happaning and maps are working ok. > > On mapserver/postgis manual (http://mapserver.org/input/vector/postgis.html) it says: > The DATA parameter is used to specify the data used to draw the map. The form of DATA is [geometry_column] from [table_name|sql_subquery] using unique [unique_key] using srid=[spatial_reference_id] . The using unique and using srid= clauses are optional, but using them improves performance. > > Is this a bug or why I cannot anymore use queries without "using unique"? > > -Pena > > > -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! > http://www.wippies.com/ > > ___ > mapserver-users mailing list > mapserver-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/mapserver-users -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Using unique
Thank's Steve, I have updated to 5.6.7, which working same way! -Pena Steve Lime [sdl...@gmail.com] kirjoitti: I've always used unique with PostGIS so it's not a surprise. The one-pass query stuff added in 5.6 probably the culprit. You might file a ticket just in case and the PostGIS devs can comment. I'm curious if 6.0 also behaves this way? (make sure to upgrade to 5.6.7!) -Steve Sent from my iPad On Jul 15, 2011, at 2:05 AM, Pena Kupen wrote: > Hi again, > > I'm answering to my by myself :-) > > When I add to the end of my query "using unique" as: > " as foobar using unique oid " > then query is again running ok. I also tried it with older version of Mapserver (5.4.2). Both versions are working ok with it. > > After some debugging of version 5.6.6, it seems to me , that in query, there is missing last right parenthesis if "using unique" is omitted. Some bug? > > -Pena > > Pena Kupen [ku...@wippies.fi] kirjoitti: >> Hello Carlos and all, >> I'm using query, which is little cryptic. >> location from ( >> select h.location, h.oid, k.expla, h.date1, h.expla, h.total, ku.locationname as name from (select pa.location, ha.type_id as oid, ha.date1, pa_n.name, pa_n.location_id, ha.car_id, ha.totalfrom pa join ha on ha.ha_pa_id = pa.pa_idleft join pa_n on pa.pa_id = pa_n.pa_idwhereha.type_id = 121557 and status='11' andha.is_valid is NULL >> ) as h >> left join ku on h.ku_id=ku.ku_id left join k on h.type_id = k.type_id where k.code='sc' and syno=false and (h.ku_id = 'AAC') >> ) as foobar >> This has been used without any problem. >> -Pena >> Carlos Ruiz [boolean10...@yahoo.com] kirjoitti: > Pena, >> > > Can you post your query ? >> > > You can use: >> > > DATA "the_geom FROM "in the traditional way >> > > > Or, if you're using a query or a view datasource, you can use: >> > > DATA "the_geom FROM | AS USING UNIQUE USING SRID= >> > > Cheers from México >> > > > IC Carlos Ruiz >> > > > > >> > From: Pena Kupen >> > To: mapserver-users@lists.osgeo.org >> > Sent: Thursday, July 14, 2011 2:59 AM >> > Subject: [mapserver-users] Using unique >> > > Sorry doubleposting! There where little "copy/paste-error". You can ignore prior... >> > > -Pena >> > > > Hello all, >> > > I'm just upgrading our system from mapserver 5.4.2 to 5.6.6. >> > In some of our layer queries, I got now error: >> > msPostGISParseData(): Query error. Error parsing PostGIS DATA variable. You must specifiy 'using unique' when supplying a subselect in the data definition. >> > When using 5.4.2, this is not happaning and maps are working ok. >> > > On mapserver/postgis manual (http://mapserver.org/input/vector/postgis.html) it says: >> > The DATA parameter is used to specify the data used to draw the map. The form of DATA is [geometry_column] from [table_name|sql_subquery] using unique [unique_key] using srid=[spatial_reference_id] . The using unique and using srid= clauses are optional, but using them improves performance. >> > > Is this a bug or why I cannot anymore use queries without "using unique"? >> > > -Pena >> > > > -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! >> > http://www.wippies.com/ >> > > ___ >> > mapserver-users mailing list >> > mapserver-users@lists.osgeo.org >> > http://lists.osgeo.org/mailman/listinfo/mapserver-users >> -- >> Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! >> http://www.wippies.com/ >> ___ >> mapserver-users mailing list >> mapserver-users@lists.osgeo.org >> http://lists.osgeo.org/mailman/listinfo/mapserver-users > > > -- > Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! > http://www.wippies.com/ > > ___ > mapserver-users mailing list > mapserver-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/mapserver-users -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] How to convert from 2393(Finish YKJ) to 4326 (WGS84)
Hello all, Last time I was successfully helped by Ralf - Thank's once more time! http://postgis.refractions.net/pipermail/postgis-users/2011-March/029055.html Now I have same type of problem, but I cannot get correct result. Table is: CREATE TABLE location ( location_id integer NOT NULL, coord geometry ); Data is in coord column as 2393 format. My sql is like: select y(location.coord) as y_ykj, x(location.coord) as x_ykj, y(ST_Transform(location.coord,4326)) as y_wgs84, x(ST_Transform(location.coord,4326)) as x_wgs84 from location where location_id = 'AAA123A' result for y_ykj and x_ykj are ok, but point of x_wgs84 and y_wgs84 are about 180meters too much in east. -Pena -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Fw: Re: [mapserver-users] How to convert from 2393(Finish YKJ) to 4326 (WGS84)
Thank's Jukka, On my spatial_ref_sys-table, there is proj4text like: +proj=tmerc +lat_0=0 +lon_0=27 +k=1.00 +x_0=350 +y_0=0 +ellps=intl +units=m +no_defs So towgs84-parameter is missing. If it is not, update the record. It this safe? Can it affect with unwanted result somewhere? You can read also http://latuviitta.org/documents/YKJ-TM35FIN_muunnos_ogr2ogr_cs2cs.txt (in Finnish, I suppose it is not a problem for you). Yeps, language not, but understanding is something else ;-) -Pena Rahkonen Jukka [jukka.rahko...@mmmtike.fi] kirjoitti: Hi, I suppose that towgs84 parameters are missing. You can check this with psql: select proj4text from spatial_ref_sys where srid=2393; The result should be close to +proj=tmerc +lat_0=0 +lon_0=27 +k=1.00 +x_0=350 +y_0=0 +ellps=intl +towgs84=-90.7,-106.1,-119.2,4.09,0.218,-1.05,1.37 +units=m +no_defs If it is not, update the record. A related thing is that Mapserver epsg text file often comes without towgs84 parameters for the Finnish KKJ zones. This is what I seem to have on this computer: KKJ / Finland Uniform Coordinate System <2393> +proj=tmerc +lat_0=0 +lon_0=27 +k=1 +x_0=350 +y_0=0 +ellps=intl +towgs84=-96.0617,-82.4278,-121.743,4.80107,0.34543,-1.37646,1.4964 +units=m +no_defs <> You can see that the parameters are not the same. There are more parameter sets around and that's actually the reason why Proj4 comes or at least used to come without any parameters because none of the sets was authorized. The results of these transformations are rather close and accurate to about one meter. You can read also http://latuviitta.org/documents/YKJ-TM35FIN_muunnos_ogr2ogr_cs2cs.txt (in Finnish, I suppose it is not a problem for you). -Jukka Rahkonen- Pena Kupen wrote: > Hello all, > Last time I was successfully helped by Ralf - Thank's once more time! http://postgis.refractions.net/pipermail/postgis-users/2011-March/029055.html > Now I have same type of problem, but I cannot get correct result. Table is: CREATE TABLE location ( location_id integer NOT NULL, coord geometry ); Data is in coord column as 2393 format. My sql is like: select y(location.coord) as y_ykj, x(location.coord) as x_ykj, y(ST_Transform(location.coord,4326)) as y_wgs84, x(ST_Transform(location.coord,4326)) as x_wgs84 from location where location_id = 'AAA123A' result for y_ykj and x_ykj are ok, but point of x_wgs84 and y_wgs84 are about 180meters too much in east. -Pena -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] How to convert from 2393(Finish YKJ) to 4326 (WGS84)
Thank's! I tried and it works ok :-) -Pena Rahkonen Jukka [jukka.rahko...@mmmtike.fi] kirjoitti: Hi, If you do not need to reproject or you are reprojecting just between KKJ zones you can live without towgs84 parameters. However, reprojecting to/from all the other systems will give an error of 150-200 meters. That is the thing that is not safe. Updating spatial_ref_sys is safe. The most official parameters are in the JHS document http://docs.jhs-suositukset.fi/jhs-suositukset/JHS153/JHS153.pdf, table in page 10. Parameters seem to be the ones I have been using with Mapserver. I do not understand the mathematics myself but luckily it is not necessary. Just use correct parameters. -Jukka Rahkonen- Pena Kupen wrote: > Thank's Jukka, > On my spatial_ref_sys-table, there is proj4text like: +proj=tmerc +lat_0=0 +lon_0=27 +k=1.00 +x_0=350 +y_0=0 +ellps=intl +units=m +no_defs So towgs84-parameter is missing. >> If it is not, update the record. >> > It this safe? Can it affect with unwanted result somewhere? >>You can read also http://latuviitta.org/documents/YKJ-TM35FIN_muunnos_ogr2ogr_cs2cs.txt (in Finnish, I suppose it is not a problem for you). >> > Yeps, language not, but understanding is something else ;-) > -Pena Rahkonen Jukka [jukka.rahko...@mmmtike.fi] kirjoitti: > Hi, > > I suppose that towgs84 parameters are missing. You can check this with psql: > > select proj4text from spatial_ref_sys where srid=2393; > > The result should be close to > +proj=tmerc +lat_0=0 +lon_0=27 +k=1.00 +x_0=350 +y_0=0 +ellps=intl +towgs84=-90.7,-106.1,-119.2,4.09,0.218,-1.05,1.37 +units=m +no_defs > If it is not, update the record. > > A related thing is that Mapserver epsg text file often comes without towgs84 parameters for the Finnish KKJ zones. This is what I seem to have on this computer: > KKJ / Finland Uniform Coordinate System > <2393> +proj=tmerc +lat_0=0 +lon_0=27 +k=1 +x_0=350 +y_0=0 +ellps=intl +towgs84=-96.0617,-82.4278,-121.743,4.80107,0.34543,-137646,1.4964 +units=m +no_defs <> > > You can see that the parameters are not the same. There are more parameter sets around and that's actually the reason why Proj4 comes or at least used to come without any parameters because none of the sets was authorized. The results of these transformations are rather close and accurate to about one meter. You can read also http://latuviitta.org/documents/YKJ-TM35FIN_muunnos_ogr2ogr_cs2cs.txt (in Finnish, I suppose it is not a problem for you). > > -Jukka Rahkonen- > > Pena Kupen wrote: > > > Hello all, > > > Last time I was successfully helped by Ralf - Thank's once more time! > http://postgis.refractions.net/pipermail/postgis-users/2011-March/029055.html > > > Now I have same type of problem, but I cannot get correct result. > > Table is: > CREATE TABLE location ( > location_id integer NOT NULL, > coord geometry > ); > > Data is in coord column as 2393 format. > > My sql is like: > select > y(location.coord) as y_ykj, > x(location.coord) as x_ykj, > y(ST_Transform(location.coord,4326)) as y_wgs84, > x(ST_Transform(location.coord,4326)) as x_wgs84 > from location where location_id = 'AAA123A' > > result for y_ykj and x_ykj are ok, but point of x_wgs84 and y_wgs84 are about 180meters too much in east. > > -Pena > > -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
