<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-devel] Prepared Geometry API</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>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<BR>
<BR>
SELECT g1.the_geom, g2.somefield, g2.the_geom<BR>
FROM g1 INNER JOIN<BR>
(SELECT somefield, ST_Collect(the_geom) as the_geom<BR>
FROM sometable GROUP BY somefield) As g2<BR>
ON ST_Intersects(g1.the_geom, g2.the_geom);<BR>
<BR>
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 -<BR>
<BR>
SELECT g1.the_geom, g2.somefield, g2.the_geom<BR>
FROM g1 INNER JOIN<BR>
(SELECT somefield, ST_Collect(the_geom) as the_geom<BR>
FROM sometable GROUP BY somefield) As g2<BR>
ON ST_Intersects(g1.the_geom, g2.the_geom, g2.ctid);<BR>
<BR>
I assumed not since it may not materialize the subselect.<BR>
-----Original Message-----<BR>
From: postgis-devel-bounces@postgis.refractions.net on behalf of David Fuhry<BR>
Sent: Mon 10/6/2008 1:39 PM<BR>
To: PostGIS Development Discussion<BR>
Subject: Re: [postgis-devel] Prepared Geometry API<BR>
<BR>
Yes, each record has a unique ctid, which can be SELECTed as a<BR>
pseudo-column.<BR>
<A HREF="http://www.postgresql.org/docs/8.3/static/ddl-system-columns.html">http://www.postgresql.org/docs/8.3/static/ddl-system-columns.html</A><BR>
ctid will be more generally usable than primary key.<BR>
<BR>
I agree with Mark on API ugliness, and sympathize with Paul on<BR>
performance. Since Tom has confirmed that there's no way for the<BR>
function to know if an arg is const for the duration of the query, a<BR>
great solution would be to go back and implement that at the db level.<BR>
Which sounds like lots of intrusive work. :(<BR>
<BR>
-Dave<BR>
<BR>
<BR>
Obe, Regina wrote:<BR>
><BR>
> Here is another thing I don't quite get why you need an id. I don't<BR>
> think PostgreSQL duplicates records when it joins, so presumably it<BR>
> already has an internal identifier<BR>
> for each record.<BR>
><BR>
> Can't we just hook onto that id somehow instead of relying on the user<BR>
> to give us one.<BR>
><BR>
> E.g. the left side of intersects will most always come from the same set<BR>
> of data<BR>
> and the right side of intersects will come from some other dataset.<BR>
><BR>
> When we pull it out of the PG_DATAUM array or whatever isn't that<BR>
> pulling it out based on some generated pointer already. Why can't we<BR>
> just compare these PG pointers and if they are the same its the same<BR>
> geometry.<BR>
><BR>
><BR>
><BR>
> -----Original Message-----<BR>
> From: postgis-devel-bounces@postgis.refractions.net on behalf of Obe, Regina<BR>
> Sent: Mon 10/6/2008 1:15 PM<BR>
> To: PostGIS Development Discussion; PostGIS Development Discussion<BR>
> Subject: RE: [postgis-devel] Prepared Geometry API<BR>
><BR>
> Paul,<BR>
> > Remember, it's not differences we're going to be finding, it's<BR>
> > similarities. My main test case was 8000 small geometries in 80 large<BR>
> > ones. That means a given spatial join did (at least) 8000 tests, and<BR>
> > for 7920 of them, the answer was "yep, the cached geometry is still<BR>
> > the same as the incoming geometry". Only in 80 of the tests was the<BR>
> > answer "geometries differ, recache!"<BR>
><BR>
> I'm sorry I still don't get it. I'm hoping a duh moment will hit me<BR>
> soon and I can be more sympathetic here. I hate not being sympathetic<BR>
> to things I don't understand.<BR>
><BR>
><BR>
> 1) So you have a cache already or is that if you were to go with this ID<BR>
> thing? I thought you already have prepared working so we have caching<BR>
> happing already no?<BR>
> 2) If you have a cache already, are we caching both the left argument<BR>
> and the right argument now or just one?<BR>
> 3) You pass the id in the first time it sees the id, you cache the<BR>
> geometry using the id as a key to pull it out later.<BR>
> 4) Second time you see the id, you just pull it from the cache? (I still<BR>
> think you need an index by the way Mark to make this efficient for large<BR>
> numbers of large geoms to efficiently pull out of the cache, but that's<BR>
> besides the point I guess)<BR>
> 5) You are making Intersects and contains and all that other stuff order<BR>
> dependent (e.g. now I have to pass in the big geometry first or second<BR>
> and the id of the big geometry)?<BR>
><BR>
><BR>
> Thanks,<BR>
> Regina<BR>
><BR>
><BR>
><BR>
><BR>
><BR>
> -----------------------------------------<BR>
> The substance of this message, including any attachments, may be<BR>
> confidential, legally privileged and/or exempt from disclosure<BR>
> pursuant to Massachusetts law. It is intended<BR>
> solely for the addressee. If you received this in error, please<BR>
> contact the sender and delete the material from any computer.<BR>
><BR>
><BR>
><BR>
><BR>
> ------------------------------------------------------------------------<BR>
><BR>
> _______________________________________________<BR>
> postgis-devel mailing list<BR>
> postgis-devel@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR>
_______________________________________________<BR>
postgis-devel mailing list<BR>
postgis-devel@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>