Re: [postgis-users] Creating an 'editable' view in PostGIS

[Richard Greenwood]

Why not just have two geometry columns in one table? One column for the
actual geometry and another for the label.

On Fri, Sep 16, 2016 at 2:36 PM, Matthew Baker  wrote:

> Hi all,
>
> I've asked this on GIS.SE, to no avail:
>
> I'd like to store label placement properties in a separate table, build a
> view of the data I want to use for my map, and use QGIS to move the labels
> manually. The idea is when the labels are updated, the placement
> coordinates would go into the LABEL_X and LABEL_Y fields in the label
> placement table, but the geometry of the underlying points and several
> other attributes (coming from the source tables) would remain unchanged.
>
> However, when all is in place, QGIS throws the following error when trying
> to save the edits to the view (the edit session can start, labels moved,
> but cannot save):
>
> Could not commit changes to layer schools_district_map
>
> Errors: ERROR: 1 attribute value change(s) not applied.
>
> Provider errors: PostGIS error while changing attributes:
>
> ERROR:  infinite recursion detected in rules for relation 
> "schools_district_map"
>
>
> Here is the definition of the view :
>
> CREATE OR REPLACE VIEW public.schools_district_map AS
>  SELECT sch.schnum,
> sch.oid,
> sch.abbreviation,
> sch.school_level,
> sch.geom,
> l.label_x,
> l.label_y
>FROM temp_schools_label sch
>  LEFT JOIN district_map_labels l ON sch.schnum = l.schnum;
>
> And here are the rules I've applied to make the view 'editable':
>
> --delete rulecreate or replace rule "delete_label" as on delete to 
> schools_district_map do insteaddelete from schools_district_map where oid = 
> old.oid;
> --insert rule
> create or replace rule "insert_label" as on insert to schools_district_map do 
> insteadinsert into schools_district_map (label_x, label_y)values 
> (new.label_x, new.label_y);
>
> --update rulecreate or replace rule "labels_update" as on UPDATE TO 
> schools_district_map do instead update schools_district_map set
> label_x = new.label_x, label_y = new.label_ywhere oid = new.oid;
>
> QGIS is then set to display the labels using the label_x and label_y field.
>
> I used this post as a guide to build the view, rules:
>
> http://gis.stackexchange.com/questions/88120/how-to-set-
> posgis-default-sequential-value-in-a-qgis-editable-view
>
> If anyone can spot where I might have left something out, or if there is a
> glaring oversight on my part, OR if this is maybe a bad idea... let me know!
>
> Thank you!!!
>
> -Matt Baker
> Denver Public Schools
> Denver, CO
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] If there any difference for snap to for lines and surfaces in Postgis Topology ?

[Lars Aksel Opsahl]

Hi


First I tested with lines and that worked find, the lines added the second time 
snapped to first lines. The data I tested with are found at.

https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/import/data3/edge_1219.shp
 .


At https://trac.osgeo.org/postgis/wiki/UsersWikiPostgisTopology I have added 
section

called “Convert shape file and simple feature data to Postgis Topology” that 
has some more info the function topo_help_sf_to_topology_case_1.


Here is the sql I tested with for lines that worked ok.


# create test schema if not exist

psql sl -c'CREATE SCHEMA IF NOT EXISTS test2;'


# copy file muni_buffer_out that contains not ok buffered out municipalitie 
border "where komid not in (125)" into table test2.sf_in (drop and create if 
exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data3/edge_1219 test2.sf_in | psql sl; 
psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_surface');"

# copy data from test2.sf_in into table topo_3.muni_surface (append data to 
topo_3.muni_edge)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_edge',0.1);"


# copy file muni_buffer_in that contains not ok buffered in municipalitie 
border "where komid not in (214)" into table test2.sf_in (drop and create if 
exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data3/edge_1521 test2.sf_in | psql sl; 
psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_surface');"

# copy data from test2.sf_in into table topo_3.muni_surface (append data to 
topo_3.muni_edge)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_edge',0.1);"


Then I tried the same with surface data and they did not snap to as I expected.


The file 
https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/import/data2/muni_ok.shp
 contains the ok surfaces, but not the 4 surfaces where I have moved the border 
data around. But all edges needed are present in this file since the surface 
not added are inclosed by other surfaces.


The I added the files “muni_buffer_in.shp muni_buffer_in_2.shp 
muni_buffer_out.shp muni_projected.shp”, my hope was that the edges for this 
surfaces should snap to the exiting edges, but that did not seem to happen.


Her is the code I tested with


# create test schema if not exist

psql sl -c'CREATE SCHEMA IF NOT EXISTS test2;'


# copy file muni_ok that contains ok municipalities border "where komid not in 
(125,214,233,301)" into table test2.sf_in (drop and create if exits)

shp2pgsql -W UTF-8 -d -D -s 4258 data2/muni_ok.shp test2.sf_in | psql sl;

# copy data from test2.sf_in into table topo_2.muni_surface (topo table 
topo_2.muni_surface will craeted on the fly if it does not exist)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface',0.1,false,'new_gid_id',true,true);"


# copy file muni_buffer_out that contains not ok buffered out municipalitie 
border "where komid not in (125)" into table test2.sf_in (drop and create if 
exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data2/muni_buffer_out test2.sf_in | psql 
sl; psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface');"

# copy data from test2.sf_in into table topo_2.muni_surface (append data to 
topo_2.muni_surface)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface',0.1);"


# copy file muni_buffer_in that contains not ok buffered in municipalitie 
border "where komid not in (214)" into table test2.sf_in (drop and create if 
exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data2/muni_buffer_in test2.sf_in | psql 
sl; psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface');"

# copy data from test2.sf_in into table topo_2.muni_surface (append data to 
topo_2.muni_surface)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface',0.1);"


# copy file muni_projected that contains not ok projectes municipalitie border 
"where komid not in (233)" into table test2.sf_in (drop and create if exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data2/muni_projected test2.sf_in | psql 
sl; psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface');"

# copy data from test2.sf_in into table topo_2.muni_surface (append data to 
topo_2.muni_surface)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface',0.1);"


# copy file muni_buffer_in_2 that contains not ok buffered in municipalitie 
border "where komid not in (301)" into table test2.sf_in (drop and create if 
exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data2/muni_buffer_in_2 test2.sf_in | psql 
sl; psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_2.muni_surface');"

# copy data from test2.sf_in 

Re: [postgis-users] fuzzy tolerance

[Lars Aksel Opsahl]

Hi again


I am not sure if this problem with drifting points are solvable by using fussy 
tolerance and simple feature in a easy way.


The main problem by using simple feature is that if a user are moving 2 points 
in a line with 100 points. Simple feature spec does not include any spec to 
mark this2 points as moved and mark the rest as unmoved. This means that the 
applications has to send all 100 points back to the server which in most causes 
means the system moves more 90% of the points. Maps are also analog data that 
makes complicated to preserve exact value, the only format that seems to 
preserve the value are WKB  and no map projections.


In stead of solving the problem with drifting point maybe it's easier just to 
walk around the problem, by only sending the part of line that has changed back 
to server. This is how we have done in the 
https://github.com/NibioOpenSource/pgtopo_update_sql that depends Postgis 
Topology. By doing it this way we remove the possibility of drifting points and 
we send less data back to the server from the client.


But before you can start to update the data you need fix your exiting data the 
way to that is by using Postgis Topology and then you can use a snap tolerance 
to ensure that you get no small slices or gaps. Here is some guide lines on way 
to this by.


Here we have two lines that almost touch each other and that we want them to be 
one single line. By using Postgis Topology this lines are melted together. The 
basic idea is that you first add the lines that are ok, then you add the rest.


# create test schema if not exist

psql sl -c'CREATE SCHEMA IF NOT EXISTS test2;'


# copy file muni_buffer_out that contains not ok buffered out municipalitie 
border "where komid not in (125)" into table test2.sf_in (drop and create if 
exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data3/edge_1219 test2.sf_in | psql sl; 
psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_surface');"

# copy data from test2.sf_in into table topo_3.muni_surface (append data to 
topo_3.muni_edge)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_edge',0.1);"


# copy file muni_buffer_in that contains not ok buffered in municipalitie 
border "where komid not in (214)" into table test2.sf_in (drop and create if 
exits)

shp2pgsql -W ISO-8859-1 -d -D -s 4258 data3/edge_1521 test2.sf_in | psql sl; 
psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_surface');"

# copy data from test2.sf_in into table topo_3.muni_surface (append data to 
topo_3.muni_edge)

psql sl -c "SELECT 
topo_help_sf_to_topology_case_1('test2.sf_in','topo_3.muni_edge',0.1);"


The scripts and data can found at 
https://github.com/NibioOpenSource/pgtopo_update_sql/tree/develop/src/test/sql/import

if somebody whats to test it.


At https://trac.osgeo.org/postgis/wiki/UsersWikiPostgisTopology I have added 
section

called “Convert shape file and simple feature data to Postgis Topology” that 
has some more info.


When all the lines are added you have build the surfaces by using 
topology.CreateTopoGeom

and using the edges added. The code for this is also found in pgtopo_update_sql 
but wrapped into different part so it takes some work to extract this code into 
single function like topo_help_sf_to_topology_case_1 . Hopefully I will get the 
time to fix this later.


Lars




Fra: Willy-Bas Loos 
Sendt: 16. september 2016 17:20
Til: PostGIS Users Discussion; Daniel Baston; Lars Aksel Opsahl
Emne: Re: [postgis-users] fuzzy tolerance

On Thu, Sep 15, 2016 at 1:58 PM, Daniel Baston 
> wrote:
> Any words of warning about using a trigger and storing the data on a 10 cm
> grid like i suggest?

Wow, thanks for the great responses. Lars Opsahl, nice to see you in the 
mailing list :D
So what i gather from this is that it is not ideal to use st_snaptogrid. It 
solves some problems, but it creates some new ones too.
Maybe a second geometry column would be a better idea, so that the original is 
still there (and will consume your server's memory  space :/ )
Anyway, there is no automatic way to solve the problem right now.

So how big are the problems that arise from this?
For me i have to say that we often have problems with errors in overlays, and 
we have to keep using st_makevalid after every step of a process. Decreasing 
the supersmall artifacts in the geometries would probably help with that.
@Lars Opsahl, you describe a lot of problems or very reasonable wishes in your 
presentation (link to abstract in previous mail). Do you think those could be 
solved with a concept similar to fuzzy tolerance?
@Daniel Baston could you describe some of the problems that the hyperprecise 
coordinates cause for you?

Cheers,

--
Willy-Bas Loos
___
postgis-users mailing list
postgis-users@lists.osgeo.org

Re: [postgis-users] fuzzy tolerance

[Stefan Keller]

When speaking about fuzzyness and tolerance we really have to
distinguish different use cases, like
1 is it about storing geometries/numbers in finite data types (like
float) => integer geometry,
2 is it about geometry representation of polygons (as geometry or topology),
3 is it about representation of uncertainty of boundaries of real-world objects,
4 or is it about handling artefacts from analysis (like in intersection).

:Stefan


2016-09-16 17:20 GMT+02:00 Willy-Bas Loos :
> On Thu, Sep 15, 2016 at 1:58 PM, Daniel Baston  wrote:
>>
>> > Any words of warning about using a trigger and storing the data on a 10
>> > cm
>> > grid like i suggest?
>
>
> Wow, thanks for the great responses. Lars Opsahl, nice to see you in the
> mailing list :D
> So what i gather from this is that it is not ideal to use st_snaptogrid. It
> solves some problems, but it creates some new ones too.
> Maybe a second geometry column would be a better idea, so that the original
> is still there (and will consume your server's memory  space :/ )
> Anyway, there is no automatic way to solve the problem right now.
>
> So how big are the problems that arise from this?
> For me i have to say that we often have problems with errors in overlays,
> and we have to keep using st_makevalid after every step of a process.
> Decreasing the supersmall artifacts in the geometries would probably help
> with that.
> @Lars Opsahl, you describe a lot of problems or very reasonable wishes in
> your presentation (link to abstract in previous mail). Do you think those
> could be solved with a concept similar to fuzzy tolerance?
> @Daniel Baston could you describe some of the problems that the hyperprecise
> coordinates cause for you?
>
> Cheers,
>
> --
> Willy-Bas Loos
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users