From: Justin D. <jde...@op...> - 2013-04-24 14:22:49
|
Thanks Jody. The problem is that by including ID's in the query distinct becomes useless because by definition ID's are unique. For distinct to work and be of any use you actually have to skip the ID's . So to make what you suggest work we would have to do multiple queries which would sort of defeat the purpose of this as a performance optimization. -Justin On Tue, Apr 23, 2013 at 9:56 PM, Jody Garnett <jod...@gm...>wrote: > Well the hint makes perfect sense … assume there are no duplicates > unless that "hint" is there to advise you that: > a) there are duplicates > b) how to handle them > > As for how to handle them - the query pulls back a feature with the same > "id" … that happen to differ based on the contents of a join. > > As for how to handle them - can we take a similar approach to how this is > done for *time*? > > > Can you handle this in the same way as we do for revisions<http://docs.geotools.org/latest/userguide/library/opengis/filter.html#identifer>? > Which amounts to a compound key … sticking some magic on the end of the > "normal" feature id. While this key is structured for revisions we could > just keep appending for the time / elevation / nD case. > > As for deciding how a DISTINCT Hint works? > Q: Can we figure out the id of rows that are being brought into the join? > Or just the randomly generated Fids? > Q: Or should we use the hint to supply column name(s) … > > -- > Jody Garnett > > On Wednesday, 24 April 2013 at 6:40 AM, Justin Deoliveira wrote: > > Hi all, > > Recently a client of ours forwarded along an initial patch that added > support for a query hint to do "distinct" queries. In this context > "distinct" implies the same semantics as the "distinct" keyword as > supported by most relational databases. The end use case of which is to > make GeoServer WMS capabilities generation more efficient with regard to > dimensions, and calculating the distinct values of a time / elevation / > custom dimension. > > In applying and cleaning up the patch I started to realize that there are > a few details making the patch not so straight forward as I originally > thought. So I would love to get some feedback before proceeding. > > So the basic idea is to add a new hint called "DISTINCT" (or something > along those lines) and then have the jdbc feature sources add it as a > supported hint. When the hint is set queries to the back end will use the > "DISTINCT" keyword. Pretty straight forward but a few questions I am > struggling with. > > 1. Hint? > > I guess the first question is whether a hint is the best way to approach > this. I am very open to alternatives here. > > 2. Feature ids > > Second question is with regard to returned features from a distinct query. > A distinct query is really a derived result set in which any primary key of > the underlying table doesn't really make sense. However to return features > we need a fid. My thought here is just to return the default randomly > generated fid for features returned from a distinct query. > > Thanks folks. Thoughts and feedback welcome. > > -Justin > > -- > Justin Deoliveira > OpenGeo - http://opengeo.org > Enterprise support for open source geospatial. > > ------------------------------------------------------------------------------ > Try New Relic Now & We'll Send You this Cool Shirt > New Relic is the only SaaS-based application performance monitoring > service > that delivers powerful full stack analytics. Optimize and monitor your > browser, app, & servers with just a few lines of code. Try New Relic > and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr > _______________________________________________ > GeoTools-Devel mailing list > Geo...@li... > https://lists.sourceforge.net/lists/listinfo/geotools-devel > > > -- Justin Deoliveira OpenGeo - http://opengeo.org Enterprise support for open source geospatial. |