[postgis-devel] Prepared Geometry API

[Obe, Regina]

Oh yah I forgot about the ctid, but that won't work for the case Mark pointed out will it where you have a sub select something like

SELECT g1.the_geom, g2.somefield, g2.the_geom
FROM g1 INNER JOIN
(SELECT somefield, ST_Collect(the_geom) as the_geom
  FROM sometable GROUP BY somefield) As g2
ON ST_Intersects(g1.the_geom, g2.the_geom);

I was thinking that internally PostgreSQL would materialize the subselect if its complex enough and internally it would generate some id.  Hmm could you actually get away with this - 

SELECT g1.the_geom, g2.somefield, g2.the_geom
FROM g1 INNER JOIN
(SELECT somefield, ST_Collect(the_geom) as the_geom
  FROM sometable GROUP BY somefield) As g2
ON ST_Intersects(g1.the_geom, g2.the_geom, g2.ctid);

I assumed not since it may not materialize the subselect.
-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net on behalf of David Fuhry
Sent: Mon 10/6/2008 1:39 PM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] Prepared Geometry API
 
Yes, each record has a unique ctid, which can be SELECTed as a 
pseudo-column. 
http://www.postgresql.org/docs/8.3/static/ddl-system-columns.html
ctid will be more generally usable than primary key.

I agree with Mark on API ugliness, and sympathize with Paul on 
performance.  Since Tom has confirmed that there's no way for the 
function to know if an arg is const for the duration of the query, a 
great solution would be to go back and implement that at the db level. 
Which sounds like lots of intrusive work. :(

-Dave


Obe, Regina wrote:
> 
> Here is another thing I don't quite get why you need an id.  I don't 
> think PostgreSQL duplicates records when it joins, so presumably it 
> already has an internal identifier
> for each record.
> 
> Can't we just hook onto that id somehow instead of relying on the user 
> to give us one.
> 
> E.g. the left side of intersects will most always come from the same set 
> of data
> and the right side of intersects will come from some other dataset.
> 
> When we pull it out of the PG_DATAUM array or whatever isn't that 
> pulling it out based on some generated pointer already.  Why can't we 
> just compare these PG pointers and if they are the same its the same 
> geometry.
> 
> 
> 
> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net on behalf of Obe, Regina
> Sent: Mon 10/6/2008 1:15 PM
> To: PostGIS Development Discussion; PostGIS Development Discussion
> Subject: RE: [postgis-devel] Prepared Geometry API
> 
> Paul,
>  > Remember, it's not differences we're going to be finding, it's
>  > similarities. My main test case was 8000 small geometries in 80 large
>  > ones. That means a given spatial join did (at least) 8000 tests, and
>  > for 7920 of them, the answer was "yep, the cached geometry is still
>  > the same as the incoming geometry". Only in 80 of the tests was the
>  > answer "geometries differ, recache!"
> 
> I'm sorry I still don't get it.  I'm hoping a duh moment will hit me 
> soon and I can be more sympathetic here.  I hate not being sympathetic 
> to things I don't understand.
> 
> 
> 1) So you have a cache already or is that if you were to go with this ID 
> thing? I thought you already have prepared working so we have caching 
> happing already no?
> 2) If you have a cache already, are we caching both the left argument 
> and the right argument now or just one?
> 3) You pass the id in the first time it sees the id, you cache the 
> geometry using the id as a key to pull it out later.
> 4) Second time you see the id, you just pull it from the cache? (I still 
> think you need an index by the way Mark to make this efficient for large 
> numbers of large geoms to efficiently pull out of the cache, but that's 
> besides the point I guess)
> 5) You are making Intersects and contains and all that other stuff order 
> dependent (e.g. now I have to pass in the big geometry first or second 
> and the id of the big geometry)?
> 
> 
> Thanks,
> Regina
> 
> 
> 
> 
> 
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20081006/bb8210ec/attachment.html>