<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Jason,<br>
<br>
It depends on what exactly you are after. <br>
<br>
<b>Solution 1:</b><br>
If you what to know what coordinates are common to two lines, then the
quickest query is to *not* use a spatial predicate. You could extract
all the points from all your lines and use GROUP BY to find all cases
where the point count is greater than one.<br>
<blockquote><tt>SELECT pointn(geom, generate_series(1,
numpoints(geom))) as pt</tt><br>
<tt>FROM my_lines</tt><br>
<tt>GROUP BY pt</tt><br>
<tt>HAVING count(DISTINCT line_id) > 1;<br>
-- WHERE line_id is a unique key on your lines<br>
-- This will not include points that are duplicate on the same line, <br>
-- ie. as in a linear ring<br>
-- If you don't care about that, use count(*) instead<br>
</tt></blockquote>
I highly recommend temporarily setting your work_mem as high as
possible to use memory instead of swap space (which will create high
I/O). (I often set this variable to use ~1.4 GB of RAM before running
such queries - mind you, I have 4GB on my server)<br>
<blockquote><tt>SET work_mem TO 1400000;</tt><br>
</blockquote>
<br>
<b>Solution 2:</b><br>
If you want to know the intersection points of your lines, then I
believe you're left with a slow spatial query.<br>
<blockquote><tt>SELECT intersection(a.geom, b.geom)</tt><br>
<tt>FROM my_lines a, my_lines b</tt><br>
<tt>WHERE a.line_id != b.line_id</tt><br>
<tt>AND a.geom && b.geom</tt><br>
<tt>AND distance(a.geom, b.geom) = 0;<br>
-- Make sure you have a spatial index on the geom column before running
this query.<br>
</tt></blockquote>
Hope this helps,<br>
Kevin<br>
<br>
<pre class="moz-signature" cols="72">--
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7
Phone: (250) 383-3022
Email: <a class="moz-txt-link-abbreviated" href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a></pre>
<br>
Jason R. Moore wrote:
<blockquote cite="mid:46DCBAEC.7000600@columbus.rr.com" type="cite">I
would like to know if their is a fast way of finding points common to
two geoms. What have are the tiger line files ...<br>
</blockquote>
<pre class="moz-signature" cols="72"></pre>
</body>
</html>