[postgis-users] spatial index for topology?

[William Kyngesburye]

Is there any way to spatially index topogeometry?

If I try to add either a gist or spgist index on a topogeometry column, I get an error:

ERROR: data type topogeometry has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

pgadmin3 doesn't allow me to choose an operator class when creating the index.

I see that the raw topology data is indexed (nodes, edges and faces), but that doesn't help the topogeometry. It looks like the whole table is being scanned when I identify a feature in QGIS.

-----
William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>
http://www.kyngchaos.com/

First Pogril: Why is life like sticking your head in a bucket filled with hyena offal?
Second Pogril: I don't know. Why IS life like sticking your head in a bucket filled with hyena offal?
First Pogril: I don't know either. Wretched, isn't it?

-HitchHiker's Guide to the Galaxy


_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Sandro Santilli]

On Sat, Oct 26, 2013 at 06:27:41PM -0500, William Kyngesburye wrote:
> Is there any way to spatially index topogeometry?

Nope, due to the fact that TopoGeometry spatial extent is not
in the value itself but derived using a volatile function from
external (referenced) data.

This is a known problem:
http://trac.osgeo.org/postgis/ticket/2124
http://trac.osgeo.org/postgis/ticket/853

One idea would be to make the && operator be
implemented by an SQL-defined function to be
fully inlined and thus giving the planner enough
hints to use the index on the underlying primitive
tables, but feasibility of that is uncertain.

Another idea would be to introduce a trigger-based
mechanism to update some form of bbox cache into
each TopoGeometry affected by any change into any
primitive table, and then use that cache in select
querys. Note that making it transparent (using &&
operator) would require a change in the TopoGeometry
structure, to add a bounding box field.

--strk;

[William Kyngesburye]

On Oct 27, 2013, at 5:22 AM, Sandro Santilli wrote:

> On Sat, Oct 26, 2013 at 06:27:41PM -0500, William Kyngesburye wrote:
>> Is there any way to spatially index topogeometry?
>
> Nope, due to the fact that TopoGeometry spatial extent is not
> in the value itself but derived using a volatile function from
> external (referenced) data.
>
> This is a known problem:
> http://trac.osgeo.org/postgis/ticket/2124
> http://trac.osgeo.org/postgis/ticket/853
>
> One idea would be to make the && operator be
> implemented by an SQL-defined function to be
> fully inlined and thus giving the planner enough
> hints to use the index on the underlying primitive
> tables, but feasibility of that is uncertain.
>
> Another idea would be to introduce a trigger-based
> mechanism to update some form of bbox cache into
> each TopoGeometry affected by any change into any
> primitive table, and then use that cache in select
> querys. Note that making it transparent (using &&
> operator) would require a change in the TopoGeometry
> structure, to add a bounding box field.
>
> --strk;

OK.

So for now, working with a large topology data set is not practical. Some processing should not be a problem, if it operates on the primitives, which are indexed.

-----
William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>
http://www.kyngchaos.com/

Earth: "Mostly harmless"

- revised entry in the HitchHiker's Guide to the Galaxy

[Christophe Vergon]

> OK.
>
> So for now, working with a large topology data set is not practical. Some processing should not be a problem, if it operates on the primitives, which are indexed.
>
> -----

I'm not sure to understand all of the problem, the way I use to link
Buissnes Object (here "tronfluv" ) and Primitives in a request is as folow

SELECT st_AsBinary(st_GetFaceGeometry('topocommune',face_id)),idtronfluv
FROM topocommune.face,topocommune.relation, schemaname.tronfluv WHERE
layer_id=:lid AND mbr && st_LineFromText(:rg,srid) AND
face_id=element_id AND (the_topo).id=topogeo_id;

Nota : :rg parameter who describe a polyline (a rectangle in fact).

It works well and relatively fast with a large topology dataset (23 518
Buissnes Object "tronfluv" in my Database)

Christophe

[Rémi Cura]

Hey,

in QGIS you will have slow visualization essentially on faces.

If your topogeom are based on node or edge it is faster.

If not, I use materialized view (requiere postgres 9.3), as a "buffer" for qgis. The problem is QGIS 2.0 doesn't support yet materialized view, so you have to create a proxy view onto your materialized view for qgis!

It is a bit of useless code but it is a great comfort gain.

Also, you can use QGis visualisation settings to make the element appears dynamically, again this is better than just waiting with blank screen

Cheers,

Rémi-C

2013/10/28 Christophe Vergon <christophev...@orange.fr>

[Sandro Santilli]

On Mon, Oct 28, 2013 at 09:48:25AM +0100, Rémi Cura wrote:
> Hey,
> in QGIS you will have slow visualization essentially on faces.
>
> If your topogeom are based on node or edge it is faster.

Any topogeometry layer is slow with QGIS, as it cannot use
an index in any case.

> If not, I use materialized view (requiere postgres 9.3), as a "buffer" for
> qgis. The problem is QGIS 2.0 doesn't support yet materialized view, so you
> have to create a proxy view onto your materialized view for qgis!
> It is a bit of useless code but it is a great comfort gain.

Interesting tip. Why doesn't QGIS support materialized views ?
Sounds like a possibly very easy thing to add, did you file a ticket
in there ?

> Also, you can use QGis visualisation settings to make the element appears
> dynamically, again this is better than just waiting with blank screen

Ohh, lots of good info here. How do you do that ?
Hey, what about writing some of these tips into the postgis wiki ?
"Working with topology in qgis"

--strk;

[Rémi Cura]

Hey,

For me face are slower because the geometry is computed each time, where lines and points geometry are just retrieved from tables.

I agree using big topology is currently difficult.
Another trick is called "clipping": use a polygon to define the area you are interested in and use spatial test to limit the number of objects to load.

QGis is far from supporting correctly Postgis layer, even with simple table/view .
For the tickets : http://hub.qgis.org/issues/8852 about one of the numerous bug.

Nonetheless, latest dev version of QGis supports materialized view : http://hub.qgis.org/issues/8791 , QGis developers are very reactive.

For the option it's in the rendering settings.

Sadly I'm not a QGis guru, but I'm often in the place where I have to use it to do something it was not intended to do (like rendering mult-millions point cloud).

I'm a wiki noob, can you provide me a link to create a new page (logged with my osgeo loggin, I couldn't find the button to add a new page)

http://trac.osgeo.org/postgis/wiki/UsersWikiMain

This page looks also out of date?

Cheers,

Rémi-C

2013/10/28 Sandro Santilli <st...@keybit.net>

[Sandro Santilli]

On Mon, Oct 28, 2013 at 02:38:54PM +0100, Rémi Cura wrote:

> I'm a wiki noob, can you provide me a link to create a new page (logged
> with my osgeo loggin, I couldn't find the button to add a new page)
> http://trac.osgeo.org/postgis/wiki/UsersWikiMain

I think you just write a CamelCase title in an existing page,
then you can click on it to edit the new page.

[Rémi Cura]

Exactly, it works !

Same problem for images though ? Shall I create a proxy ticket to host images about using QGIS?

Cheers,

Rémi-C

2013/10/29 Sandro Santilli <st...@keybit.net>

[Rémi Cura]

Hey,

here is a draft for a page about using QGIS with PostGIS

Anybody can edit it .

http://trac.osgeo.org/postgis/wiki/UsersWikiQGIS

Cheers,

Rémi-C

2013/10/29 Rémi Cura <remi...@gmail.com>

[Sandro Santilli]

On Tue, Oct 29, 2013 at 04:03:47PM +0100, Rémi Cura wrote:
> Hey,
> here is a draft for a page about using QGIS with PostGIS
>
> Anybody can edit it .
>
> http://trac.osgeo.org/postgis/wiki/UsersWikiQGIS

Thank you, very useful.

I actually expected something specific for topology,
but it's surely a good start !

PS: replacing PostGis with PostGIS would remove all those prompts
to create a PostGis table.

--stk;

[Paolo Cavallini]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Il 30/10/2013 12:46, Sandro Santilli ha scritto:
> On Tue, Oct 29, 2013 at 04:03:47PM +0100, Rémi Cura wrote:
>> Hey, here is a draft for a page about using QGIS with PostGIS
>>
>> Anybody can edit it .
>>
>> http://trac.osgeo.org/postgis/wiki/UsersWikiQGIS
>
> Thank you, very useful.

??
PostGIS srid are not supported, so you have to manually choose the
right projection and coordinate system when adding a PostGIS geometry
layer.

all the best.
- --
Paolo Cavallini - Faunalia
www.faunalia.eu
Full contact details at www.faunalia.eu/pc
Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.15 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlJw/PUACgkQ/NedwLUzIr5fbwCfS8U5eIXdZ0noncTHyArpfgfc
UREAn3VI1Clq4iyrJtvC0BbSLim07m6N
=DKFs
-----END PGP SIGNATURE-----

[Rémi Cura]

Hey,

done for the anoying "PostGis".

regarding topology, I always use ::geometry , so using topology is using plain PostGIS geometry for me.

Do you have something specific in mind?

Cheers,

Rémi-C

2013/10/30 Sandro Santilli <st...@keybit.net>

[Sandro Santilli]

On Wed, Oct 30, 2013 at 01:36:23PM +0100, Rémi Cura wrote:
> Hey,

> done for the anoying "PostGis".
>
> regarding topology, I always use ::geometry , so using topology is using
> plain PostGIS geometry for me.
> Do you have something specific in mind?

As of QGIS-2.0.0 there's:

- Native support for TopoGeometry layers (no need to make a query,
"Add PostGIS Layer" will let you select TopoGeometry fields)
- Topology viewer in DBManager: select a topology schema and see
view topology (or similar) in the menu.
- A postgis topology editor plugin (Experimental)

--strk;