[postgis-users] newbie question and geometries?

[Kevin Neufeld]

You will want to filter your query with a WHERE clause.

Without it, you are computing a cross-join where most of the interection 
points are empty geometry collections.

Try:
SELECT ST_Intersection( roads.segment, streams.segment) AS clvt
FROM roads, streams
WHERE ST_Intersects( roads.segment, streams.segment);

Cheers,
Kevin

Jeff D. Hamann wrote:
> Hi,
> 
> Let me first apologize in advance for the newbie question, but I haven't
> found a satisfactory answer to this question on the web yet, which is not
> to say there isn't one. I just haven't found it yet. My simple question is
> this:
> 
> I have two line geometries (e.g. roads and streams) and want to create a
> layer of points where the two intersect. The simple query I've got is:
> 
> drop table culvert_locations;
> create table
>        culvert_locations
> as
> select
>        Intersection( roads.segment, streams.segment ) as clvt
> from
>      roads, streams;
> 
> which returns the same number of rows as the roads table. It's a geometry
> collection (I think) as evident by the the_geom field:
> 
> 010700000000000000
>  010700000000000000
>  0101000000C36A11E61F789A4012108DEC17DA9340
>  0101000000FAC5DC1431C19A407B94A5CFE4109440
>  010700000000000000
>  010700000000000000
>  010700000000000000
>  010700000000000000
>  0101000000EAEBBE471202A240DE619E6B1B039B40
>  010700000000000000
>  010700000000000000
>  010700000000000000
>  010700000000000000
> 
> 
> and when I attempt to export the points using:
> 
> pgsql2shp -f cullocs -r -u postgres ch1 culvert_locations
> 
> I get the following results:
> 
> pgsql2shp -f cullocs -r -u postgres ch1 culvert_locations
> Initializing... type 'GEOMETRYCOLLECTION' is not Supported at this time.
> The DBF file will be created but not the shx or shp files.
> You've found a bug! (pgsql2shp.c:2864)
> make: *** [sqldump] Error 1
> jeff-hamanns-macbook-pro:ch1 hamannj$
> 
> Did I really find a bug, or am I not constructing the result table, and
> thus the shapefile correctly. And on that note, I'm trying to maintain all
> the data inside PostGIS (which rocks!) rather than having to export to
> shapefiles, which are being used for the development.
> 
> Thanks, and please help.
> 
> Jeff.
>