[SpatiaLite for android] Stmt.step() too slow

[周超]

my table have 3407 records.

sql : SELECT AsBinary(SimplifyPreserveTopology(Geometry, ?)) FROM polygon WHERE ROWID IN (SELECT pkid FROM idx_polygon_Geometry WHERE MBRintersects(buildmbr(xmin,ymin,xmax,ymax),buildmbr(?,?,?,?)));

code :

Stmt stmt = db.prepare(sql);

while (stmt.step())

  xxxx

traceview:

how to improve performance?

[Mark Bradford]

On Sat, Dec 8, 2012 at 6:12 AM, 周超 <yzlk...@gmail.com> wrote:
>
> my table have 3407 records.
>
> sql : SELECT AsBinary(SimplifyPreserveTopology(Geometry, ?)) FROM polygon WHERE ROWID IN (SELECT pkid FROM idx_polygon_Geometry WHERE MBRintersects(buildmbr(xmin,ymin,xmax,ymax),buildmbr(?,?,?,?)));
>
> code :
> Stmt stmt = db.prepare(sql);
> while (stmt.step())
> xxxx
>
> traceview:
>

> how to improve performance?

(hint) http://www.sqlite.org/lang_transaction.html

[周超]

sry I dont understand. My stmt is SELECT. How to code?

在 2012年12月8日星期六UTC+8下午10时00分37秒，markb写道：

[a.fu...@lqt.it]

> SELECT AsBinary(SimplifyPreserveTopology(Geometry, ?))
> FROM polygon
> WHERE ROWID IN (
> SELECT pkid
> FROM idx_polygon_Geometry
> WHERE
> MBRintersects(buildmbr(xmin,ymin,xmax,ymax),buildmbr(?,?,?,?))
> );
>

the above SQL query doesn't seems to access the Spatial Index
in an efficient way.
rewriting the SQL query as follows seems to be more reasonable:

SELECT AsBinary(SimplifyPreserveTopology(Geometry, ?))
FROM polygon
WHERE ROWID IN (

SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'polygon'
AND search_frame = BuildMbr(?,?,?,?)
);

bye Sandro


--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[周超]

same, still slow

在 2012年12月10日星期一UTC+8下午9时02分26秒，sandro furieri写道：

[Micha Silver]

Hi:

On 13/12/2012 02:51, 周超 wrote:

same, still slow

在 2012年12月10日星期一UTC+8下午9时02分26秒，sandro furieri写道：

> SELECT AsBinary(SimplifyPreserveTopology(Geometry, ?))
> FROM polygon
> WHERE ROWID IN (
>     SELECT pkid
>     FROM idx_polygon_Geometry
>     WHERE
> MBRintersects(buildmbr(xmin,ymin,xmax,ymax),buildmbr(?,?,?,?))
> );
>

Sorry to butt in, but why do you need to even use the the spatial index in your above query? What do you expect to gain? It seems you are trying to create a simplified geometry for *each* polygon, so I don't understand what querying the MBR could add?
The limiting factor for performance with Simplify is, I believe,  the tolerance factor (marked above as a '?' in your function). Did you try to play with that?

the above SQL query doesn't seems to access the Spatial Index
in an efficient way.
rewriting the SQL query as follows seems to be more reasonable:

SELECT AsBinary(SimplifyPreserveTopology(Geometry, ?))
FROM polygon
WHERE ROWID IN (
     SELECT ROWID
     FROM SpatialIndex
     WHERE f_table_name = 'polygon'
         AND search_frame = BuildMbr(?,?,?,?)
);

bye Sandro


--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/spatialite-users/-/mBzkl1NOkjcJ.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

-- 
Micha Silver
GIS Consulting
http://www.surfaces.co.il

[周超]

the situation : I want use SpatiaLite in an android app. 

MBRintersects(buildmbr(xmin,ymin,xmax,ymax),buildmbr(?,?,?,?)

it use to only query the data about the current screen can rendering.

 you are right. 

SimplifyPreserveTopology

it cause slow. I remove it, 10s -> 1s, but complex geometry cause rendering slow.

I think not a good way to solve it.

在 2012年12月13日星期四UTC+8下午3时19分36秒，Micha写道：

[a.fu...@lqt.it]

Hi Micha,

> Sorry to butt in, but why do you need to even use the the spatial
> index in your above query? What do you expect to gain? It seems you
> are trying to create a simplified geometry for *each* polygon, so I
> don't understand what querying the MBR could add?
> The limiting factor for performance with Simplify is, I believe, 
> the tolerance factor (marked above as a '?' in your function). Did
> you
> try to play with that?
>

the '?' notation is a fully legitimate one for "prepared statements";
it simply is a positional placeholder intended to be effectively
replaced by some external parameter before actual execution.
http://www.sqlite.org/c3ref/bind_blob.html

so the real problem is: which values are used to define the MBR ?
search_frame = BuildMbr(?,?,?,?)

if this MBR is nearly equal to the layer full extent using the
Spatial Index is completely useless.
but if this MBR is small enough to effectively filter just
few polygons then using the Spatial Index makes perfectly sense.

[a.fu...@lqt.it]

>> SimplifyPreserveTopology
>
> it cause slow. I remove it, 10s -> 1s, but complex geometry cause
> rendering slow.
> I think not a good way to solve it.
>

all right, now the problem is more clearly defined.
it's not at all a Spatial Index issue, it's a rendering issue.

attempting to use Simplify isn't a good solution, because it
simply transfers the workload from the rendering engine to the
DBMS engine.
please note: Simplify is a rather costly operation to be computed.

creating once for all a derived table (or column) containing
already simplified geometries could probably be a smarter
solution.