#! /bin/sh
#
# mk_test : script to create tables, load test data & run queries to
#           demonstrate 0 length tring issue
#
# v0.1 B Wood April 2005


#change the vairables as required

DB=env2003
LINE_TAB=lines
CELL_TAB=cells


echo "make table to store line data"
#drop existing table
psql $DB -c "delete from geometry_columns where f_table_name='$LINE_TAB';"
psql $DB -c "drop table $LINE_TAB"

psql $DB -c "create table $LINE_TAB ( event_key  integer);"

#add geometry column
echo "add geometry for tow lines"
psql -d $DB -c "select AddGeometryColumn('public', '$LINE_TAB', 'the_geom', 4326, 'LINESTRING', 2);"


echo "make table to store cells"
#drop existing table
psql $DB -c "delete from geometry_columns where f_table_name='$CELL_TAB';"
psql $DB -c "drop table $CELL_TAB"

psql $DB -c "create table $CELL_TAB ( cell_id  integer);"

#add geometry column
echo "add geometry for cells"
psql -d $DB -c "select AddGeometryColumn('public', '$CELL_TAB', 'the_geom', 4326, 'POLYGON', 2);"

echo "Tables should be built OK, press Enter to continue"
read JUNK


#load data....
echo "loading lines"
LINES="13683355|SRID=4326;LINESTRING(175.5 -44.2,175.5 -44.2)
13677419|SRID=4326;LINESTRING(178.68333 -42.9833,178.68333 -42.9833)
12583686|SRID=4326;LINESTRING(175.01667 -43.9833,175.01667 -43.9833)
12578235|SRID=4326;LINESTRING(175.23333 -43.95,175.23333 -43.95)
12497906|SRID=4326;LINESTRING(181.65 -44.2,181.65 -44.2)
12497919|SRID=4326;LINESTRING(175.26667 -43.95,175.26667 -43.95)
12497936|SRID=4326;LINESTRING(174.98333 -43.8333,174.98333 -43.8333)
12497956|SRID=4326;LINESTRING(176.38333 -43.8666,176.38333 -43.8666)"

echo "$LINES" | psql $DB -c "copy $LINE_TAB
                             from STDIN with delimiter '|';"

psql $DB -c "create index line_geom_idx on lines using gist(the_geom);"



echo "loading cells"
CELLS="3553|SRID=4326;POLYGON((175.45 -44.25,175.45 -44.2,175.5 -44.2,175.5 -44.25,175.45 -44.25))
3554|SRID=4326;POLYGON((175.45 -44.2,175.45 -44.15,175.5 -44.15,175.5 -44.2,175.45 -44.2))
3625|SRID=4326;POLYGON((175.5 -44.25,175.5 -44.2,175.55 -44.2,175.55 -44.25,175.5 -44.25))
3626|SRID=4326;POLYGON((175.5 -44.2,175.5 -44.15,175.55 -44.15,175.55 -44.2,175.5 -44.2))
8186|SRID=4326;POLYGON((178.65 -43,178.65 -42.95,178.7 -42.95,178.7 -43,178.65 -43))
2910|SRID=4326;POLYGON((175 -44,175 -43.95,175.05 -43.95,175.05 -44,175 -44))
3198|SRID=4326;POLYGON((175.2 -44,175.2 -43.95,175.25 -43.95,175.25 -44,175.2 -44))
3199|SRID=4326;POLYGON((175.2 -43.95,175.2 -43.9,175.25 -43.9,175.25 -43.95,175.2 -43.95))
12481|SRID=4326;POLYGON((181.65 -44.25,181.65 -44.2,181.7 -44.2,181.7 -44.25,181.65 -44.25))
12482|SRID=4326;POLYGON((181.65 -44.2,181.65 -44.15,181.7 -44.15,181.7 -44.2,181.65 -44.2))
12409|SRID=4326;POLYGON((181.6 -44.25,181.6 -44.2,181.65 -44.2,181.65 -44.25,181.6 -44.25))
12410|SRID=4326;POLYGON((181.6 -44.2,181.6 -44.15,181.65 -44.15,181.65 -44.2,181.6 -44.2))
3270|SRID=4326;POLYGON((175.25 -44,175.25 -43.95,175.3 -43.95,175.3 -44,175.25 -44))
3271|SRID=4326;POLYGON((175.25 -43.95,175.25 -43.9,175.3 -43.9,175.3 -43.95,175.25 -43.95))
2841|SRID=4326;POLYGON((174.95 -43.85,174.95 -43.8,175 -43.8,175 -43.85,174.95 -43.85))
4856|SRID=4326;POLYGON((176.35 -43.9,176.35 -43.85,176.4 -43.85,176.4 -43.9,176.35 -43.9))"

echo "$CELLS" | psql $DB -c "copy $CELL_TAB
                             from STDIN with delimiter '|';"

psql $DB -c "create index cell_geom_idx on cells using gist(the_geom);"



echo "OK, test data loaded, now run the queries to demonstrate the problem" 
echo "Press Enter to continue"
read JUNK


echo ""
echo ""

psql $DB -c "select distinct event_key,
                    cell_id,
                    x(startpoint(l.the_geom)),
                    y(startpoint(l.the_geom)),
                    c.the_geom
             from cells c,
                  lines l
             where c.the_geom && l.the_geom;"


echo "This retrieves all the matches correctly (I think)" 
echo "Press Enter to continue"
read JUNK

echo ""
echo ""



echo "&& & intersects etc"
psql $DB -c "select event_key,
                    c.cell_id,
                    x(startpoint(l.the_geom)),
                    y(startpoint(l.the_geom)),
                    c.the_geom
             from cells c,
                  lines l
             where c.the_geom && l.the_geom
               and ( contains(l.the_geom, c.the_geom)
                     or
                     touches(l.the_geom, c.the_geom)
                     or
                     crosses(l.the_geom, c.the_geom)
                     or
                     intersects(l.the_geom, c.the_geom));"


echo "this failed to retrieve several matches I think it should have???" 
echo " eg: line #12497906 at 181.65 -44.2 lies on the corner of four"
echo "     adjacent cells ( 12409, 12410, 12481, 12482 ) and this point can"
echo "     be seen to be in the cell definitions, so should be found with"
echo "     relate, intersects or touches, but is not"
echo ""
echo " I'm not sure if this is how a zero length line should be treated"
echo " or not, but it isn't how I expected it to be treated"
echo ""
echo " Thanks for any help you can offer"
echo "     Brent :-)"