Databases and MapServer

[Hallgren Johan E]

Hello Steve 

I hope you understood that the issue was when data are stored in a
database (Oracle, SDE). I could not use shapefiles with large layers
(over a million records), it was especially poor for attribute queries.

Do you mean that my "query" (polygon + distance + attribute) could be
done with MasServer and OracleSpatial or SDE? Have I missed that? What a
bout the performance?

Regarding non-DB sources I'm just positive, it works great.

I read the mail from Fernando and he was also thinking of extending the
MapServer Query mechanism. 

Just let me know if you need any assistance. It would be nice to see if
you planning to add new features/functions.

Best Regards

___________________________________


Johan Hallgren
WM-data


Pelle Bergs backe 3
Box 1938, 791 19 Falun
Tel: 023-844 65 (int: +46-2384465)
Mobil: 070-588 44 28 (int: +46-705884428)
johan.e.hallgren at wmdata.com
http://www.wmdata.se


-----Original Message-----
From: Steve Lime [mailto:steve.lime at dnr.state.mn.us] 
Sent: den 14 mars 2006 15:39
To: hobu at iastate.edu; fsimon at univali.br; Hallgren Johan E
Cc: dmorissette at dmsolutions.ca; warmerdam at pobox.com
Subject: RE: [UMN_MAPSERVER-DEV] Databases and MapServer

I believe the architecture can be extended to support more complex
queries. SDE may be the hardest of the DBs because of the way ESRI wants
you to build filters. Getting to this point may actually simplify life.
Of course handling the non-DB sources may ultimately prove challanging,
but if GEOS is introduced into filter definition (I talked way back with
Dave Blasby about this) we could do funky stuff with non-DB sources too.

The query you mention is possible to a certain extent now with
MapScript.

Steve

>>> "Hallgren Johan E" <jhhal at wmdata.com> 03/14/06 2:05 AM >>>
Hello Howard,

Yes it could work but I haven't tried it. There are two things I bother
on that are important for me, and the first is the possibility to do
more complex queries. 

I can ask it again; is it possible (for example) to do a spatial query
with a polygon and a buffer, and optionally combine that with an
attribute query? I will just ensure that mapserver supports different
options of queries supported by the specific database used. Or at least,
that mapserver have an architecture that can be extended without rebuild
everything.

And the other ting is that this should be supported by OracleSpatial
also (I haven't seen any conversation related to that option). 

Regards
Johan

___________________________________


Johan Hallgren
WM-data


Pelle Bergs backe 3
Box 1938, 791 19 Falun
Tel: 023-844 65 (int: +46-2384465)
Mobil: 070-588 44 28 (int: +46-705884428)
johan.e.hallgren at wmdata.com
http://www.wmdata.se

-----Original Message-----
From: Howard Butler [mailto:hobu at iastate.edu] 
Sent: den 13 mars 2006 17:20
To: Hallgren Johan E; Steve Lime; fsimon at univali.br
Cc: dmorissette at dmsolutions.ca; warmerdam at pobox.com
Subject: RE: [UMN_MAPSERVER-DEV] Databases and MapServer

Johan,

Is something like this going to be sufficient?
http://mapserver.gis.umn.edu/bugs/show_bug.cgi?id=1708

It has been added to CVS HEAD if you want to try it out.

Howard

At 02:32 AM 3/13/2006, Hallgren Johan E wrote:
>I will try to explain.
>
>I have tried a database approach for storing spatial data and display
>with mapserver. The layer had about a million polygon features.
>Displaying those was not a problem but when I should do a spatial query
>there was really bad performance. In my case, I saw that the query
>direct in the database was much faster so I tried to find out what the
>difference was, what I saw was.
>
>In my query I did not care about the extent, I just want to query a
>single row/id. In MapServer the extent was always added so a query on a
>single row combined with an extent added a lot to the retrieval time. I
>tried to find out a way to se if the extent was the same as the
>layer-extent and in that case, skip the spatial part of the extent. It
>would be better if the extent could have a null value and in that case
>skip it. I also saw a need to manage other spatial operators, like
>polygons. My image was that both Oracle Spatial and SDE had a lot of
>possibilities but way few of these was available at thru mapserver.
>
>Does it make thing clearer?
>
>Regards
>Johan
>
>___________________________________
>
>
>Johan Hallgren
>WM-data
>
>
>Pelle Bergs backe 3
>Box 1938, 791 19 Falun
>Tel: 023-844 65 (int: +46-2384465)
>Mobil: 070-588 44 28 (int: +46-705884428)
>johan.e.hallgren at wmdata.com
>http://www.wmdata.se
>
>
>-----Original Message-----
>From: Howard Butler [mailto:hobu at iastate.edu]
>Sent: den 10 mars 2006 20:12
>To: Hallgren Johan E; Steve Lime; fsimon at univali.br
>Cc: dmorissette at dmsolutions.ca; warmerdam at pobox.com
>Subject: RE: [UMN_MAPSERVER-DEV] Databases and MapServer
>
>I'm confused.  What is it we were trying to do here again?  Just
>check for a null extent and not attempt to query with the layer's
>extent if we were doing spatial queries?
>
>At 03:02 PM 3/2/2006, Hallgren Johan E wrote:
> >Yes, you are right. I saw it solely from my perspective but you have
> >better knowledge over the MapServer and the architecture. To
continue,
> >how could these improvements be achieved? Could a new set of
arguments
> >be added to the function quite soon, and the actual implementation be
a
> >"longer term" work? Or could I expect that it suddenly works as I
>wish:)
> >
> >I'm not the right person to say if a single stage query mechanism or
> >two-stage ditto is best, but I can say that for me a single query is
> >more logical. From my C# perspective I don't see how two stage
queries
> >should look like with resultCacheMemberObj and so on, but maybe
that's
> >just lack of knowledge.
> >
> >I'm interested in how this will "go on".
> >
> >
> >Best Regards
> >Johan
> >___________________________________
> >
> >
> >Johan Hallgren
> >WM-data
> >
> >
> >Pelle Bergs backe 3
> >Box 1938, 791 19 Falun
> >Tel: 023-844 65 (int: +46-2384465)
> >Mobil: 070-588 44 28 (int: +46-705884428)
> >johan.e.hallgren at wmdata.com
> >http://www.wmdata.se
> >
> >
> >-----Original Message-----
> >From: Steve Lime [mailto:steve.lime at dnr.state.mn.us]
> >Sent: den 1 mars 2006 23:22
> >To: fsimon at univali.br
> >Cc: dmorissette at dmsolutions.ca; hobu at hobu.net; warmerdam at pobox.com;
> >Hallgren Johan E
> >Subject: Re: [UMN_MAPSERVER-DEV] Databases and MapServer
> >
> >I don't think the fact that msWhichShapes is used for drawing and in
> >query functions is bad. Quite the contrary, the fact the same
function
> >is used for query and drawing is a good since they do the same thing-
> >identify candidate features for further processing. (FILTERs in the
> >mapfile are nothing more than attribute queries) I do think the
drivers
> >just need to get better at optimizing what is passed off to the
>database
> >and ignoring the spatial filter in certain instances is one of those
> >optimizations. Shapefiles already do this I believe.
> >
> >The two-stage query mechanism is another problem altogether. One
> >potential idea is to extend msLayerWhichShapes (or add other similar
> >functions) to deal with more complex filters such as feature
> >intersections. That way msLayerWhichShapes (really the driver
specific
> >version) would set things up so that repeated calls to msNextShape
>could
> >be used to retrieve features just once. (msLayerGetShape would go
away
> >in this case)
> >
> >It get's complicated though since executing a query and actually
> >processing the result set are not done one right after another. Plus
>add
> >feature post classification with classObj expressions, feature
queries
> >(features from one layer used to query others) and so on. Two stages
> >hurt performance but are *very* flexible in what you can do.
> >
> >Steve
> >
> > >>> Fernando Simon <fsimon at univali.br> 03/01/06 3:03 PM >>>
> >Hi folks,
> >      I go back from a holiday just this afternoon. I'm reading my
> >e-mails since last Friday just now, sorry for the delay.
> >      For Mapserver I believe that the problem is because the same
> >functions are used for query and draw modes. The Mapserver core call
>the
> >
> >same function to retrieve tabular and spatial data. And the problem
>that
> >
> >Johan report occur in this moment.
> >      In maporaclespatial.c I used the SDO_FILTER operator to
restrict
> >the data in the query, so the spatial data that Mapserver reads is
the
> >data that are inside of the map extents. But when a query is executed
> >this function is executed too. In my opinion, we can pass a flag or
>pass
> >
> >null for the extents(rect) in query modes to fix it, and after we
just
> >need to change a little the data drivers (Oracle, SDE, PostGIS,
Shape,
> >OGR) to check for null extents and don't use it in the query's.
Another
> >way is create separate functions for query and draw methods.
> >      Another point that I want to report is a issue with the query
> >methods, I spoke with Daniel when he come here last year in the User
> >Meeting, did you remember Daniel? When a query is executed the
>Mapserver
> >
> >call a function to query the data and call another function to
retrieve
> >the tabular data for all returned records. And for every call to this
> >function Mapserver needs to reconnect in the data source. Daniel do
you
> >have more ideas about this point?
> >      Johan, did you change the maporaclespatial.c? Can you send me
>your
> >changes?
> >      Best regards.
> >
> >Fernando Simon
> >
> >
> >Steve Lime wrote:
> > > Howard and I are talking off-list about SDE specifically. This is
>not
> >just a query issue, but a drawing one as well. Often you can be
zoomed
> >out well beyond the extents of a layer and in that case it doesn't
make
> >sense to apply a spatial constraint at all since the selection
> >rect/layer rect overlap test suffices. I'm pretty sure the shapefile
> >code works this way and just uses the FILTER (which is all an
attribute
> >query is) if present. Otherwise all features are returned. The same
> >logic should work with SDE, Oracle Spatial and everything else for
that
> >matter.
> > >
> > > I've already seen the SDE patch, and the changes for Oracle should
>be
> >passed along (to Fernando at a minimum) too.
> > >
> > > I'm now using both SDE and Oracle Spatial at work so that frees up
> >some time to implement.
> > >
> > > Steve
> > >
> > >
> > >>>> Frank Warmerdam <warmerdam at pobox.com> 03/01/06 7:38 AM >>>
> > >>>>
> > > Hallgren Johan E wrote:
> > >
> > >> Hello again,
> > >>
> > >> I hope you have got my email from yesterday regarding
improvements
>in
> >
> > >> the attribute query functionality in SDE and OracleSpatial. This
is
> > >> improvements I have to deal with if we should continue to work
with
> > >> MapServer.
> > >>
> > >
> > > Johan,
> > >
> > > I skimmed it, but this isn't my area of specialization so I didn't
> > > comment.
> > >
> > >
> > >> Today I have made some changes to OracleSpatial also. I know my
> >solution
> > >> isn't optimal but se it as a workaround right now. If you would
>have
> >my
> > >> code, just let me know.
> > >>
> > >> I have to mention that what I really would like to have is better
> > >> functionality for attribute/spatial queries when working with
> >databases.
> > >> It could be either spatial or attribute or a combination of these
>in
> >a
> > >> query. For shapefiles its better, but as I can see it, it doesn't
> >work
> > >> for databases; the performance is just to poor. As I can see a
> >function
> > >> like layerObj.QueryDatabase(string query, geometry shape) would
be
> >good.
> > >> This mapscript-function should be directly mapped to a c-function
>for
> >
> > >> respectively database.
> > >>
> > >> I would appreciate if you could comment my initiatives, so I know
>if
> >my
> > >> work is valuable.
> > >>
> > >
> > > I think it is important to be able to issue queries without a
>spatial
> > > constraint and the difficulty of doing so currently is a defect of
> > > MapServer and should be corrected.
> > >
> > > I don't know how best to fix it, and what the implications of such
> >changes
> > > might be.  But if you are willing to work on it that would seem to
>be
> > > useful.
> > >
> > > I do think we need more test suite tests around this area before
we
> >change
> > > too much.  To the best of my knowledge there are no automated
tests
> >that
> > > address attribute queries, but I could be wrong.
> > >
> > > If changes to how queries are done need to be address in the
raster
> >query
> > > or mapogr.cpp, I would be willing to help out.  I'm pretty afraid
of
> > > the SDE, Oracle and PostGIS code though.
> > >
> > > Best regards,
> > >