Mark Volz | 26 Nov 23:14 2014
Picon
Picon

Re: Getting Started with PostGIS on Windows: Request for documentation review.

Regina and others.

Thank you for your assistance.  I updated my documentation at
http://geomoose.lyonco.org/PostGIS_New_Documentation.docx to incorporate your comments.  The
documentation works, but there are a couple items I would like to clear up.

1) Just to be sure we need to assign default permissions to both the database and the schema?
2) My older notes mention that ArcGIS is only compatible with PostGRES 9.2.x, and not 9.3.  Has anyone had any
success with 9.3?
3) It does appear that I cannot edit default permissions using pgAdmin 1.16.1.  I don't know if that is a bug,
or a Windows version issue.  You mentioned that you have pgAdmin 1.18.1 will allow you to edit default
privileges for all users in the default privileges tab.  Is that the version that comes with PostGRES 9.3?  

Everything now appears to be stable, secure, and repeatable.  Thank you again for your help.

Sincerely,
Mark Volz, GISP

> -----Original Message-----
> 
> Mark,
> 
> A couple of things I would change in the document
> 
> 1) I wouldn't use template_postgis_20 and in fact if you are running 2.1, that is
> the wrong template to use anyway.
> You should instead create a database the normal way via pgAdmin with
> template0 or default template1
> 
> Then do:
(Continue reading)

Luís Miguel Royo Pérez | 26 Nov 19:14 2014
Picon

Doubts dissolving geometries

Hi everyone,

I'm trying to make a quite simple query in Postgis but I have problems with it. I just want to create a buffer dissolved of a set of points. Only that.

This is the query I'm using:

SELECT  emt_paradas.id , ST_union(st_buffer(emt_paradas.geom, 500)) FROM emt_paradas

but when I try to execute this, tells me the field  emt_paradas.id must be in the clause group by but if I do that, the dissolve is not done.

I'm in the DBManager of QGIS 2.6. I want to create this layer and load it in the canvas, for that DBManager asks me for an ID field, that's why I added emt_paradas.id in the select. If i remove it, Postgis creates the geometrybut I can't load it in the canvas.

Any help or guidance will be apreciate.

Thanks a lot!!



_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
toni hernández | 26 Nov 11:17 2014

rounding off geometry coordinates

Hello,

I need to round off all the coordinates of some polygon geometries.
So far I am using the st_asgeojson function to use the precision I need, 
and then I create a new geometry from the GeoJSon I have just created.

Something like this:
SELECT ST_ST_GeomFromGeoJSON(ST_AsGeoJSON(value_old,geom_precision))

Is there a more efficent way to do this?.
khorvat | 26 Nov 09:59 2014
Picon

strange result of spatial functions

Before two days i had problem with conversion of MultiLineString to
LineString

http://postgis.17.x6.nabble.com/st-union-and-st-linemerge-returns-multilinestring-td5007358.html
<http://postgis.17.x6.nabble.com/st-union-and-st-linemerge-returns-multilinestring-td5007358.html> 
. Later I found out that this problem appears with more spatial functions.

For eg. result I get from ST_LineMerge with geometry from database is
different then result of ST_LineMerge with geometry read as ST_GeomFromText
(they have same points, and are in same reference system).

Also I made small script that test geometries:

DO $$
DECLARE
	v_boundaryUnion		geometry;
	v_unitBoundary		geometry;
BEGIN
	SELECT ST_Union(b."Geometry")
		INTO v_boundaryUnion
		FROM myschema."Boundary" a
		INNER JOIN myschema."BoundaryGeom" b
			ON b."BoundaryId" = a."BoundaryId"
		WHERE a."UnitId" = 1;
	
	RAISE NOTICE 'BOUNDARY UNION: %', ST_AsText(v_boundaryUnion);
	
	
	SELECT ST_Boundary("Geometry")
		INTO v_unitBoundary
		FROM myschema."UnitGeom"
		WHERE "UnitId" = 1;
		
	RAISE NOTICE 'UNION BOUNDARY: %', ST_AsText(v_unitBoundary);
	
	
	IF ST_Equals(v_boundaryUnion, v_unitBoundary) THEN
		RAISE NOTICE 'EQUALS';
	ELSE
		RAISE NOTICE 'DIFFERENCE';
	END IF;
	
	v_boundaryUnion := ST_GeomFromWKB(ST_AsBinary(v_boundaryUnion), 3765);
	RAISE NOTICE 'UNION FROM WKB: %', ST_AsText(v_boundaryUnion);
	
	IF ST_Equals(v_boundaryUnion, v_unitBoundary) THEN
		RAISE NOTICE 'EQUALS';
	ELSE
		RAISE NOTICE 'DIFFERENCE';
	END IF;
	
	
	v_boundaryUnion := ST_GeomFromText(ST_AsText(v_boundaryUnion), 3765);
	RAISE NOTICE 'UNION FROM WKT: %', ST_AsText(v_boundaryUnion);
	
	IF ST_Equals(v_boundaryUnion, v_unitBoundary) THEN
		RAISE NOTICE 'EQUALS';
	ELSE
		RAISE NOTICE 'DIFFERENCE';
	END IF;
	
END $$;

result of this script is:
EQUALS
EQUALS
DIFFERENCE

Later on if I make operations with geometry from wkt i get expected results
(eq. ST_Difference), same as in JTS, and with original geometry I have some
really strange behaviour.

I don't have any idea why this thing happens. Metadata seems to be OK also:
f_table_name, f_geometry_column, coord_dimension, srid, type
"UnitGeom";"Geometry";2;3765;"GEOMETRY"
"BoundaryGeom";"Geometry";2;3765;"LINESTRING"

Any idea what could be wrong?

--
View this message in context: http://postgis.17.x6.nabble.com/strange-result-of-spatial-functions-tp5007385.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
Dave Potts | 25 Nov 23:53 2014
Picon

Getting x km of a linestring expressed in projection (3857)


I like to extra the first x km from a linestring geometry with an srid 
id of 3857, but I having problems working out how to do it.

I known its possible to split  a line using st_lineSubString, but that 
only works in fractions off a length,  I am interested in get it 
projection unit lengths, are there any better suggestions??

Dave.
George Merticariu | 25 Nov 17:32 2014
Picon

Raster data questions

Hello!

I want to use PostGIS for handling 3D cubes but I couldn't figure out how to do it from the manual. 

The main tasks I want to accomplish are:

1. Import a 1D char array file (grey cube) into a 3D cube.
2. Retrieve sections from the cube, where a section is defined by a domain.

Example:

Given a file of 1024*1024*1024 bytes, I want to import it into a cube with the domain [0:1023, 0:1023, 0:1023]. Then, select the sub-domains (examples):
  •  [100:200, 100:200, 0:100]
  •  [0:1, 0:1023, 0:1023] 

Is this possible using PostiGIS? If yes, are there any detailed tutorials which explain how to do that?

Thank you!

Best regards,
George Merticariu

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Oliver Burgfeld | 25 Nov 07:08 2014
Picon

Creating trajectory/lines from millions of points [PostGIS]

Hi,

I have millions of points in a PostGIS database containing taxi gps tracks. Now I want to create lines from these points by vehicleid and ordered by timestamp. But, and that's my problem right now, at first I want to include every column of my point table into the "line table" and I also need to intersect those lines at specific points.

I have one column representing the "taxi_is_occupied" status with 0 or 1.

What I want now is to create lines which are divided every time this status changes. In the end I need lines which show the path of every taxi over time, divided every time the status of the car changes so that I can query all lines where the taxi is occupied, for example.

What do I have to use therefore? I know that there is the ST_MakeLines tool existing in PostGIS, but as I am a new PostGIS user... I do not know exactly how to use it to get the results I need.


Thanks a lot

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Paul & Caroline Lewis | 24 Nov 18:33 2014
Picon

PLPGSQL Function to Calculate Bearing

Basically I can't get my head around the syntax of plpgsql and would appreciate some help with the following efforts.
I have a table containing 1000's of wgs84 points. The following SQL will retrieve a set of points within a bounding box on this table:

    SELECT id, ST_X(wgs_geom), ST_Y(wgs_geom), ST_Z(wgs_geom) FROM points_table INNER JOIN (SELECT ST_Transform(ST_GeomFromText('POLYGON((-1.73576102027 51.5059743629,-1.73591122397 51.5061067655,-1.73548743495 51.5062838333,-1.73533186682 51.5061514313,-1.73576102027 51.5059743629))',4326),) AS bgeom) AS t2 ON ST_Within(local_geom, t2.bgeom)

What I need to do is add a bearing/azimuth column to the results that describes the bearing at each point in the returned data set.
So the approach I'm trying to implement is to build a plpgsql function that can select the data as per above and calculate the bearing between each set of points in a loop.
However my efforts at understanding basic data access and handling within a plpgsql function are failing miserably.

An example of the current version of the function I'm trying to create is as follows:

    CREATE TYPE bearing_type AS (x numeric, y numeric, z numeric, bearing numeric);
    DROP FUNCTION IF EXISTS get_bearings_from_points();
    CREATE OR REPLACE FUNCTION get_bearings_from_points()
    RETURNS SETOF bearing_type AS
    $BODY$
    DECLARE
        rowdata points_table%rowtype;
        returndata bearing_type;
    BEGIN
        FOR rowdata IN
            SELECT nav_id, wgs_geom FROM points_table INNER JOIN (SELECT ST_Transform(ST_GeomFromText('POLYGON((-1.73576102027 53.5059743629,-1.73591122397 53.5061067655,-1.73548743495 53.5062838333,-1.73533186682 53.5061514313,-1.73576102027 53.5059743629))',4326),27700) AS bgeom) AS t2 ON ST_Within(local_geom, t2.bgeom)
        LOOP
            returndata.x := ST_X(rowdata.wgs_geom);
            returndata.y := ST_Y(rowdata.wgs_geom);
            returndata.z := ST_Z(rowdata.wgs_geom);
            returndata.bearing := ST_Azimuth(<current_point> , <next_point>)
        RETURN NEXT returndata;
        END LOOP;
        RETURN;
    END
    $BODY$
    LANGUAGE plpgsql;

I should just be able to call this function as follows:

    SELECT get_bearings_from_points();

and get the desired result.
Basically the problems are understanding how to access the rowdata properly such that I can read the current and next points.

In the above example I've had various problems from how to call the ST_X etc SQL functions and have tried EXECUTE select statements with errors re geometry data types.

Thanks

Paul


Any insights/help would be much appreciated
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
khorvat | 24 Nov 15:51 2014
Picon

st_union and st_linemerge returns multilinestring

Hi list,

I have a problem with ST_Union and ST_LineMerge functions. I have following
query
SELECT ST_LineMerge(ST_Union(b."Geometry"))
	INTO v_union
	FROM schema."TableA" a
	INNER JOIN schema."TableB" b
		ON b."Id" = a."Id"
	WHERE a."DetailId" = 1;

Which returns 
MULTILINESTRING((301565.3582 5032543.1448,301520.4023
5032575.3387,301505.3992 5032587.1594,301478.4221 5032610.8667,301463.6862
5032640.3163,301440.7183 5032695.9716,301429.9559 5032719.8098,301422.2228
5032728.0427,301412.2265 5032733.2853,301400.7336 5032735.2842,301389.2586
5032735.2842,301381.2581 5032733.2854,301371.4223 5032726.5435,301357.9516
5032716.0579,301344.4631 5032704.0815,301315.4902 5032676.6307,301306.2425
5032670.3885,301292.2728 5032656.4133,301282.7756 5032652.1699,301267.0419
5032644.4368,301243.575 5032638.94,301211.1989 5032641.3075,301162.0735
5032649.6878,301047.3405 5032666.6039,300933.1243 5032681.6605,300871.3122
5032684.4785,300848.5937 5032684.4621,300772.0575 5032670.0314),(300772.0575
5032670.0314,300779.9036 5032718.2534,300793.8376 5032810.3296,301171.4304
5032787.5317,301219.6082 5032755.2239,301241.9853 5032782.5577,301306.0927
5032787.715,301388.1035 5032778.9493,301456.6398 5032820.2146,301546.1875
5032818.6355,301607.7843 5032966.1217,301683.8773 5032939.0456,301759.9702
5032908.5129,301785.3346 5032898.1433,301827.9927 5032872.7954,301843.0408
5032861.6841,301834.9513 5032821.7736,301826.7937 5032798.6776,301821.0173
5032791.5784,301812.6782 5032784.5007,301796.4991 5032773.3844,301784.6142
5032762.776,301768.934 5032746.8593,301755.8017 5032734.4814,301742.4023
5032717.3031,301737.0924 5032709.9796,301734.5467 5032703.7167,301740.8835
5032690.4582,301745.24 5032681.5532,301752.7649 5032671.8567,301771.181
5032651.4742,301791.7917 5032626.7147,301772.865 5032612.8079,301730.5037
5032602.1471,301712.5406 5032601.8038,301666.7967 5032634.3862,301652.4077
5032628.2238,301623.9236 5032603.2804,301634.4155 5032575.3248,301614.3054
5032522.4277))

As you can see last point of first line is same as first point of the second
line (...,300772.0575 5032670.0314),(300772.0575 5032670.0314,...). Problem
with MultiLineString is next step where I need difference of two lines. If
parameter is MultiLineString I get wrong result (I have no idea why, I tired
same thing in JTS and it works fine). For some reason PostGIS
(ST_Difference) returns expected result just for LineString parameters.

Also if I create geometry from text it will return LineString after applying
ST_LineMerge function, so problem appears only for select from table.

DO $$
DECLARE
	v_geom			geometry;
BEGIN
	
	v_geom := ST_GeomFromText('MULTILINESTRING((301565.3582
5032543.1448,301520.4023 5032575.3387,301505.3992 5032587.1594,301478.4221
5032610.8667,301463.6862 5032640.3163,301440.7183 5032695.9716,301429.9559
5032719.8098,301422.2228 5032728.0427,301412.2265 5032733.2853,301400.7336
5032735.2842,301389.2586 5032735.2842,301381.2581 5032733.2854,301371.4223
5032726.5435,301357.9516 5032716.0579,301344.4631 5032704.0815,301315.4902
5032676.6307,301306.2425 5032670.3885,301292.2728 5032656.4133,301282.7756
5032652.1699,301267.0419 5032644.4368,301243.575 5032638.94,301211.1989
5032641.3075,301162.0735 5032649.6878,301047.3405 5032666.6039,300933.1243
5032681.6605,300871.3122 5032684.4785,300848.5937 5032684.4621,300772.0575
5032670.0314),(300772.0575 5032670.0314,300779.9036 5032718.2534,300793.8376
5032810.3296,301171.4304 5032787.5317,301219.6082 5032755.2239,301241.9853
5032782.5577,301306.0927 5032787.715,301388.1035 5032778.9493,301456.6398
5032820.2146,301546.1875 5032818.6355,301607.7843 5032966.1217,301683.8773
5032939.0456,301759.9702 5032908.5129,301785.3346 5032898.1433,301827.9927
5032872.7954,301843.0408 5032861.6841,301834.9513 5032821.7736,301826.7937
5032798.6776,301821.0173 5032791.5784,301812.6782 5032784.5007,301796.4991
5032773.3844,301784.6142 5032762.776,301768.934 5032746.8593,301755.8017
5032734.4814,301742.4023 5032717.3031,301737.0924 5032709.9796,301734.5467
5032703.7167,301740.8835 5032690.4582,301745.24 5032681.5532,301752.7649
5032671.8567,301771.181 5032651.4742,301791.7917 5032626.7147,301772.865
5032612.8079,301730.5037 5032602.1471,301712.5406 5032601.8038,301666.7967
5032634.3862,301652.4077 5032628.2238,301623.9236 5032603.2804,301634.4155
5032575.3248,301614.3054 5032522.4277))', 3765);
	
	RAISE NOTICE '%', ST_AsText(ST_LineMerge(v_geom));
END $$;

Any idea what causes this problem?

--
View this message in context: http://postgis.17.x6.nabble.com/st-union-and-st-linemerge-returns-multilinestring-tp5007358.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
R. Paul Warriner | 23 Nov 18:10 2014

st_intersection function does not exist after upgrade

Does anyone know if the following are spurious errors, hopefully not requiring attention, or should I scrap everything, and dust off the T-square…? J

Things appear normal, but I haven’t done any raster work, yet.

 

After an upgrade of a database from 9.1 to 9.3, I received the errors below, going:

 

From (which started as a 1.5, and had manual raster configurations at 2.0) -

"POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.0.0 r9605" need upgrade) TOPOLOGY (topology procs from "2.0.0 r9605" need upgrade) RA (...)"

 

To-

"POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER"

 

ERROR:  function st_intersection(raster, raster, regprocedure) does not exist

ERROR:  function st_intersection(raster, raster, text, regprocedure) does not exist

ERROR:  function st_intersection(raster, integer, raster, integer, regprocedure) does not exist

ERROR:  function st_intersection(raster, integer, raster, integer, text, regprocedure) does not exist

 

Regards,

Paul

 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Ahmet Temiz | 16 Nov 13:27 2014
Picon

postgis table from another postgis table in different projection?

How can I create  postgis table from another postgis table in different projection?


regards


--
Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Planlama ve Zarar Azaltma Dairesi Başkanlığı


________________________

Ahmet Temiz
Geological Eng.
Information Systems - GIS Group
Disaster and Emergency Management
of Presidency
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Gmane