st_contains between 2 tables polygon / point
Ted37
Sorry for my english.
I'm a beginner with spatialite.
I've got 2 tables, the first is a polygonal table (2763 rows) and the
second is a point table (3082 rows).
I wish realise a query between the two tables, because I wish find
points that are content in each polygon (and the inverse).
I've tested this query :
SELECT *
from "7492_carte_sondes", "Buffer_sondes"
where st_contains("Buffer_sondes.Geometry",
"7492_carte_sondes.Geometry")
But the result is 8 billion rows, it's strange, because I know that I
should have between 2000 rows and 3000 rows.
How do it?
Best regards
a.fu...@lqt.it
this is quite puzzling, because your query is a valid one.
the only enhancement I can suggest you is using a SpatialIndex,
so to get an impressive speed up. e.g.:
SELECT * FROM mypolygs, mypoints
WHERE ST_Contains(mypolygs.geom, mypoints.geom) = 1
AND mypoints.ROWID IN (
SELECT ROWID FROM SpatialIndex
WHERE f_table_name = 'mypoints'
AND search_frame = mypolygs.geometry);
just two possible flaws to be checked:
a) both tables *must* be in the same Reference System (SRID-value)
b) testing if ST_Contains() actually return 1 (TRUE) always is a good
idea
bye Sandro
--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.
Ted37
I have tested the srid of 2 ways :
The first with spatialite "show spatiale metadata" and the result is
the same for the 2 tables (srid : 4326)
The second with GlobalMapper, where i loaded my 2 shapes and the
result is ok!
I generated spatialIndex, but i don't understand. For each table, I
have 4 news tables :
For my table of points (7492_carte_sondes) :
idx_7492_carte_sondes_GEometry (pkid, xmin, xmax, ymax)
idx_7492_carte_sondes_Geometry_node (nodeno, data)
idx_7492_carte_sondes_Geometry_parent(node, parentnode)
idx_7492_carte_sondes_Geometry_rowid(rowid, nodeno)
For my table of polygons (buffer_sondes) :
idx_Buffer_sondes_Geometry(pkid, xmin, xmax, ymax)
idx_Buffer_sondes_Geometry_node(nodeno, data)
idx_Buffer_sondes_Geometry_parent(node, parentnode)
idx_Buffer_sondes_Geometry_rowid(rowid, nodeno)
but for the query that you proposed me, what the elements SpatialINdex
(maybe idx_7492_carte_sondes_Geometry)
but f_table_name and search_frame, I don't know!
Could you tell me few explications.
thanks a lot
bye
a.fu...@lqt.it
SpatialIndex is a "virtual table"; any SpatiaLite's own DB surely
contains a "SpatialIndex" table, intended to make simpler and easier
using a spatial index in your queries:
http://www.gaia-gis.it/gaia-sins/SpatialIndex-Update.pdf
If your DB has no SpatialIndex table at all, this probably means that
you are currently using some archaic, obsolete and deprecated version;
and in this case upgrading to some more recent release (e.g. 3.x.x)
is strongly suggested.
Ted37
bye
a.fu...@lqt.it
I suggest you to switch to the most recent spatialite-gui v.1.5.0
I ignore what operating system you are using: anyway, you can
download Windows binaries from here:
http://www.gaia-gis.it/gaia-sins/
and if you are on Linux you can easily build from yourself using
the latest sources (available on the same download site)
Ted37
I continued my tests with 2 tables light (2 rows).
In spatialite, I loaded 2 shapefiles (ENC table point and PAPIER table
point, all with SRID 4326).
I built a table buffer with this explication :
Create table test_buffer ((id integer Primary key autoincrement, Name
text, description text);
SELECT AddGeometryColumn('test_buffer', 'geometry',4326, 'POLYGON',
'XY');
INSERT INTO 'test_buffer' (id, geometry, Name) select Null, st_buffer
(geometry, 0.000009), Name, description from "ENC";
The result is 4 rows, however for my test, it does have only one.
I'm sure that my datas didn't corrupt!
I tried the same query with arcgis (with my shapefiles) and the result
is true.
I would like that Spatialite enable me to obtain the same result,
because I didn't necessary Arcgis or others GIS.
Best regards
Brad Hards
> Hi Sandro,
>
> I continued my tests with 2 tables light (2 rows).
> In spatialite, I loaded 2 shapefiles (ENC table point and PAPIER table
> point, all with SRID 4326).
> I built a table buffer with this explication :
> Create table test_buffer ((id integer Primary key autoincrement, Name
> text, description text);
> SELECT AddGeometryColumn('test_buffer', 'geometry',4326, 'POLYGON',
> 'XY');
> INSERT INTO 'test_buffer' (id, geometry, Name) select Null, st_buffer
> (geometry, 0.000009), Name, description from "ENC";
Why a Null ID?
> The result is 4 rows, however for my test, it does have only one.
Can you show the real results? Its a bit difficult to tell what the
issues could be without real information.
> I tried the same query with arcgis (with my shapefiles) and the result
> is true.
Can you show the real results here too?
Brad
a.fu...@lqt.it
> Can you attach the input for this simple test?
>
+1
> Why a Null ID?
>
because it's INTEGER PRIMARY KEY AUTOINCREMENT
that's fine, the SQLite core will automatically replace
this NULL using the next available id-value
> Can you show the real results here too?
>
+1 (again)
and in addtion: can you kindly tell us which spatialite's
version and which operating system are you using ?
Jukka Rahkonen
to reach something else. This happens every now and then when
something goes wrong with the logic when making joins between tables.
I am not good enough in seeing what happens with your query but very
obviously every single geometry in the first table is now compared
with all the geometries in the second table and each comparison yields
a row. Like in this picture http://fi.wikipedia.org/wiki/Karteesinen_tulo
-Jukka Rahkonen-
Ted37
> Can you attach the input for this simple test?
> Can you show the real results here too?
> and in addtion: can you kindly tell us which spatialite's
> version and which operating system are you using ?
Windows 2000
a.fu...@lqt.it
>> Can you attach the input for this simple test?
>
yes, you can send your sample DB to my private mail address;
and includinging a short but clear description of any step you've
attempted since now surely helps.
bye sandro
a.fu...@lqt.it
;-)
[really interesting problem after all, because it give us a nice chance
to explain some SQL subtleties ..]
the original Teddy's query
--------------------------
SELECT *
from "PAPIER", "test_buffer"
where st_contains("test_buffer.Geometry",
"PAPIER.Geometry");
======
the resultset contains 4 rows, i.e. it simply is the
cartesian product of both tables
another slightly modified query
-------------------------------
SELECT *
from "PAPIER", "test_buffer"
where st_contains("test_buffer.Geometry",
"PAPIER.Geometry") = 1;
======
this time the resultset is empty: really puzzling, isn't ?
where is the problem ?
quite simple to identified ... for en expert eye, but probably
not at all so obvious for a beginner ;-)
the expected SQL syntax was instead:
------------------------------------
SELECT *
from "PAPIER", "test_buffer"
where st_contains("test_buffer"."Geometry",
"PAPIER"."Geometry") = 1;
======
the resultset now contains one unique row, as expected :-D
please carefully pay attention on double-quotes.
Post Mortem:
============
accordingly to SQL syntax rules, "test_buffer"."Geometry"
is a valid reference to the value of a column named
"Geometry" within a table named "test_buffer".
"test_buffer.Geometry" is a completely different thing,
it simply is a TEXT CONSTANT, not at all a column name.
Accordingly to strictly pedantic SQL syntax rules a Text Constant
is expected to be written as 'test_buffer.Geomeyry' (please
note, single-quoted).
anyway many SQL engines (this including SQLite) will
indifferently accept as well any Text Constant enclosed
within double-quotes.
just a quick debug/check:
SELECT ST_Contains("test_buffer.Geometry", "PAPIER.Geometry");
==========
the result is: -1
and the ST_Contains doc says:
"The return type is Integer, with a return value of 1 for TRUE,
0 for FALSE, and –1 for UNKNOWN corresponding to a function
invocation on NULL arguments"
neither the first nor the second arg are valid Geometries (they
simply are Text Constants), and accordingly to this -1 is returned.
but -1 isn't a synonym for FALSE, in boolean terms it's TRUE ...
and this fully explains why Teddy's initial query simply returned
the cartesian product :-D
Lessons to learn:
=================
never use double-quoted names if there isn't any compelling
reason to do this; this simply risk to confuse you.
SELECT *
from PAPIER, test_buffer
where st_contains(test_buffer.Geometry,
PAPIER.Geometry);
this simplest query will work nicely, and there aren't any confusing
double-quotes ;-)
bye Sandro
Ted37
Thanks a lot for the explication!
I'll try to do mistakes more difficult ;-)
this mistakes are trainer!
best regards