Re: [mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join
Hi Dana, Usually it does not matter much, as mapserver passes the query to Postgis anyway, as you noted. However, mapserever does do some parsing & rewriting of the query before issuing it, and this can cause issues. Also note that when working in the db, it is easier to use explain, check indexes, etc to optimise your query, so if it is anything but a simple join, I'd suggest creating it in the database is the tidier way to do this. I guess I approach it with the premise that mapserver is the rendering engine, Postgis is the data management/query engine. If your dba is going to be able to see the views/queries/joins which the db implements, & manage/optimise the db for these, it is much easier if they are inplemented in the db than scattered around in mapfiles. This said, I use where clauses all the time in mapfiles, for layers based on data driven categories within a single table, for example, map LAYER DTIS = "select ... where category='DTIS';", etc. I'd suggest views in this case are just clutter :-) It is a case-by-case basis... for informed advice, seeing the actual query would be useful . Cheers, Brent Wood --- On Fri, 4/29/11, Dara Olson wrote: From: Dara Olson Subject: [mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join To: mapserver-users@lists.osgeo.org Date: Friday, April 29, 2011, 6:34 AM Greetings. I am hoping that I have posted this on the most appropriate list, please let me know if I should be posting to a different list. In our Mapserver application, we join a lot of tables together - generally one table with geometry to a "flat" table with tabular data. My question is - is it more efficient/faster to create a view in PostgreSQL doing the join and link to the view table from Mapserver or to define the join (SQL statement) with Mapserver in the data definition within the layer definition? Does it even matter because Mapserver sends the request to PostgreSQL/GIS? Also, we have joins that take a field within the flat table to determine which table to join to get geometry. For example, if the flat table column "join" value is equal to 1 then it joins to "table1" to get geometry or if column "join" value is equal to 2 then it joins to "table2" to get geometry. I read something that you were not supposed to use WHERE clauses in the Mapserver data definition. Would this be better to do as a view and link to the view table from the data definition? Any advice would be greatly appreciated. Thanks in advance! Dara -Inline Attachment Follows- ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join
Thanks so much! - Original Message - From: Andy Colson To: Dara Olson Cc: mapserver-users@lists.osgeo.org Sent: Thursday, April 28, 2011 1:56 PM Subject: Re: [mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join On 4/28/2011 1:34 PM, Dara Olson wrote: > Greetings. I am hoping that I have posted this on the most appropriate > list, please let me know if I should be posting to a different list. > In our Mapserver application, we join a lot of tables together - > generally one table with geometry to a "flat" table with tabular data. > My question is - is it more efficient/faster to create a view in > PostgreSQL doing the join and link to the view table from Mapserver or > to define the join (SQL statement) with Mapserver in the data definition > within the layer definition? Does it even matter because Mapserver sends > the request to PostgreSQL/GIS? > Also, we have joins that take a field within the flat table to determine > which table to join to get geometry. For example, if the flat table > column "join" value is equal to 1 then it joins to "table1" to get > geometry or if column "join" value is equal to 2 then it joins to > "table2" to get geometry. I read something that you were not supposed to > use WHERE clauses in the Mapserver data definition. Would this be better > to do as a view and link to the view table from the data definition? > Any advice would be greatly appreciated. Thanks in advance! > Dara > > > > ___ > mapserver-users mailing list > mapserver-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/mapserver-users > is it more efficient/faster to create a view in > PostgreSQL doing the join and link to the view table from Mapserver or > to define the join (SQL statement) with Mapserver in the data definition > within the layer definition? I doubt a view would be faster or slower. I'd bet it would be the same. (Engine wise its going to process the same sql statement, collect/join the data the same way, etc). > I read something that you were not supposed to > use WHERE clauses in the Mapserver data definition. Where? I disagree with that totally. > Would this be better > to do as a view and link to the view table from the data definition? 1) performance wise, no, I doubt it because: create view test1 as select * from fname where id < 5; Then: "select * from test1" and "select * from fname where id < 5" are processed exactly the same way... and it would even be the same as: create view test2 as select * from fname; select * from test2 where id < 5; performance wise, the backend processes them all the same way. 2) usage wise, no because the view limit's your usage of the where. You cannot pass or change arguments: create view test1 as select * from fname where id < 5; There is no way to change the "id < 5" condition. You can't pass it as an argument to the view, and you can't modify it without recreating the view. I'd say its okay to stay with sql in your mapfile. The only time I have used views is when I need to query different tables with different names/columns in a clean consistent way. I sometimes get data that has a column parcel_number, or PID, or parcelNum or ... I create a view for each database, then my view can use the actual name, but my mapfile always looks the same: select pin, the_geom from parcels; Another good use for view's is if you want to hide some of the complexity of all the joins. It might make your mapfile look a little nicer and easier to edit. But dont put the where's inside the view... because then everyone is stuck with it. You mapfile should look like: select ...,the_geom from view where something = x; -Andy___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join
On 4/28/2011 1:34 PM, Dara Olson wrote: Greetings. I am hoping that I have posted this on the most appropriate list, please let me know if I should be posting to a different list. In our Mapserver application, we join a lot of tables together - generally one table with geometry to a "flat" table with tabular data. My question is - is it more efficient/faster to create a view in PostgreSQL doing the join and link to the view table from Mapserver or to define the join (SQL statement) with Mapserver in the data definition within the layer definition? Does it even matter because Mapserver sends the request to PostgreSQL/GIS? Also, we have joins that take a field within the flat table to determine which table to join to get geometry. For example, if the flat table column "join" value is equal to 1 then it joins to "table1" to get geometry or if column "join" value is equal to 2 then it joins to "table2" to get geometry. I read something that you were not supposed to use WHERE clauses in the Mapserver data definition. Would this be better to do as a view and link to the view table from the data definition? Any advice would be greatly appreciated. Thanks in advance! Dara ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users > is it more efficient/faster to create a view in > PostgreSQL doing the join and link to the view table from Mapserver or > to define the join (SQL statement) with Mapserver in the data definition > within the layer definition? I doubt a view would be faster or slower. I'd bet it would be the same. (Engine wise its going to process the same sql statement, collect/join the data the same way, etc). > I read something that you were not supposed to > use WHERE clauses in the Mapserver data definition. Where? I disagree with that totally. > Would this be better > to do as a view and link to the view table from the data definition? 1) performance wise, no, I doubt it because: create view test1 as select * from fname where id < 5; Then: "select * from test1" and "select * from fname where id < 5" are processed exactly the same way... and it would even be the same as: create view test2 as select * from fname; select * from test2 where id < 5; performance wise, the backend processes them all the same way. 2) usage wise, no because the view limit's your usage of the where. You cannot pass or change arguments: create view test1 as select * from fname where id < 5; There is no way to change the "id < 5" condition. You can't pass it as an argument to the view, and you can't modify it without recreating the view. I'd say its okay to stay with sql in your mapfile. The only time I have used views is when I need to query different tables with different names/columns in a clean consistent way. I sometimes get data that has a column parcel_number, or PID, or parcelNum or ... I create a view for each database, then my view can use the actual name, but my mapfile always looks the same: select pin, the_geom from parcels; Another good use for view's is if you want to hide some of the complexity of all the joins. It might make your mapfile look a little nicer and easier to edit. But dont put the where's inside the view... because then everyone is stuck with it. You mapfile should look like: select ...,the_geom from view where something = x; -Andy ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join
On 4/28/2011 2:34 PM, Dara Olson wrote: Greetings. I am hoping that I have posted this on the most appropriate list, please let me know if I should be posting to a different list. In our Mapserver application, we join a lot of tables together - generally one table with geometry to a "flat" table with tabular data. My question is - is it more efficient/faster to create a view in PostgreSQL doing the join and link to the view table from Mapserver or to define the join (SQL statement) with Mapserver in the data definition within the layer definition? Does it even matter because Mapserver sends the request to PostgreSQL/GIS? Also, we have joins that take a field within the flat table to determine which table to join to get geometry. For example, if the flat table column "join" value is equal to 1 then it joins to "table1" to get geometry or if column "join" value is equal to 2 then it joins to "table2" to get geometry. I read something that you were not supposed to use WHERE clauses in the Mapserver data definition. Would this be better to do as a view and link to the view table from the data definition? Any advice would be greatly appreciated. Thanks in advance! Dara Dara, I don't think it matter if you create views or not from a performance point of view. View do allow you to hide much of the internals from the mapfile but that might not matter. The follow are some random examples of postgis DATA statements from some of my mapfiles. If you want to do complex queries then you need to wrap them into sub-query and that can have joins, where, union whatever you need in it. DATA "the_geom from (select gid, name, the_geom from lebanon_data.ai union all select gid, name, the_geom from data.ai) as foo using unique gid using srid=4326" DATA "the_geom from (select gid, coalesce(key::text,'') as key, feature, coalesce(st_type_abbr,' ') as st_type_abbr, coalesce(civic_num,' ') as civic_num, coalesce(street_name,' ') as street_name, the_geom from v_polygon) as foo using unique gid using srid=2036" DATA "the_geom from (select id, chk, the_geom from vertices_tmp where chk=1 and the_geom && setsrid(!BOX!, 4326) ) as foo using SRID=4326 using unique id" The !BOX! feature might be a custom patch I create or a standard mapserver feature I can't remember off the top of my head and don't have time at the moment to check into that, but what is does is substitute the text to define a box representing the image extents so I can place the in the subquery rather than the main query for performance reasons as the vertices_tmp table is huge. -Steve W ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join
Greetings. I am hoping that I have posted this on the most appropriate list, please let me know if I should be posting to a different list. In our Mapserver application, we join a lot of tables together - generally one table with geometry to a "flat" table with tabular data. My question is - is it more efficient/faster to create a view in PostgreSQL doing the join and link to the view table from Mapserver or to define the join (SQL statement) with Mapserver in the data definition within the layer definition? Does it even matter because Mapserver sends the request to PostgreSQL/GIS? Also, we have joins that take a field within the flat table to determine which table to join to get geometry. For example, if the flat table column "join" value is equal to 1 then it joins to "table1" to get geometry or if column "join" value is equal to 2 then it joins to "table2" to get geometry. I read something that you were not supposed to use WHERE clauses in the Mapserver data definition. Would this be better to do as a view and link to the view table from the data definition? Any advice would be greatly appreciated. Thanks in advance! Dara___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
