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
Paragon Corporation | 16 Nov 05:41 2014
Picon

FOSS4G NA 2015 - Last day for submissions November 17th

Just a friendly reminder to folks who aren't aware.

The FOSS4G NA 2015 conference will be held in the San Francisco, California
area this year: March 9th-12th.
PostgreSQL Day will be co-hosted with it on March 10th 2015.

Talk submission deadline for FOSS4GNA 2015 ends this Monday November 17th.
Accepted speakers get free admission to the main conference.

Hint Hint: dustymugs

Thanks,
Regina Obe
FOSS4G NA 2015 Program Committee Member 
PostGIS PSC Member
http://www.postgis.us
http://postgis.net
Martin Landa | 13 Nov 18:49 2014
Picon

geocoding

Hi all,

I would like to geocode addresses in my DB which are located in Czech
Republic. I was thinking to use Nominatim, does anyone here have an
experience with such task?

Thanks in advance, Martin

--

-- 
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.eu/mentors/landa
Andy Anderson | 12 Nov 17:33 2014
Picon

Geographic Type Documentation Correction

Hi, I just noticed that on this page:


there are a number of references to “sphere” when they should instead say “spheroid”, and to “great circle arc” when they should instead say “geodesic”.

This is clear from the subsequent discussion, in particular in the FAQ section:


where it says 

4.2.3.1.

Do you calculate on the sphere or the spheroid?

By default, all distance and area calculations are done on the spheroid. You should find that the results of calculations in local areas match up will with local planar results in good local projections. Over larger areas, the spheroidal calculations will be more accurate than any calculation done on a projected plane.

All the geography functions have the option of using a sphere calculation, by setting a final boolean parameter to 'FALSE'. This will somewhat speed up calculations, particularly for cases where the geometries are very simple.


So in

4.2. PostGIS Geography Type

the corrected documentation would change:

The basis for the PostGIS geographic type is a sphere. The shortest path between two points on the sphere is a great circle arc. That means that calculations on geographies (areas, distances, lengths, intersections, etc) must be calculated on the sphere, using more complicated mathematics. For more accurate measurements, the calculations must take the actual spheroidal shape of the world into account, and the mathematics becomes very complicated indeed.

to:

The basis for the PostGIS geographic type is a spheroid, a “squashed” sphere whose polar diameter is smaller than its equatorial diameter. The shortest path between two points on the sphere is a great circle arc, and on a spheroid it’s a similar arc called a geodesic. That means that calculations on geographies (areas, distances, lengths, intersections, etc) will provide more accurate measurements, but the calculations must take the actual spheroidal shape of the world into account, and the mathematics becomes very complicated indeed.

and in

4.2.3.3. What is the longest arc you can process?

the corrected documentation would change:

We use great circle arcs as the "interpolation line" between two points. That means any two points are actually joined up two ways, depending on which direction you travel along the great circle. All our code assumes that the points are joined by the *shorter* of the two paths along the great circle. As a consequence, shapes that have arcs of more than 180 degrees will not be correctly modelled.

to:

We use geodesics as the "interpolation line" between two points (these are great circle arcs on spheres). That means any two points are actually joined up two ways, depending on which direction you travel between them. All our code assumes that the points are joined by the *shorter* of the two paths. As a consequence, shapes that have arcs of more than 180 degrees will not be correctly modeled.

Cheers,

— Andy

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Rémi Cura | 12 Nov 17:41 2014
Picon

no .bat in windows zip installers

Hey everybody.
The 2 bat files
makepostgisdb_using_extensions.bat
and
makepostgisdb.bat

seems to have vanished from the zip release file for version newer than 2.1.1 (postgis-bundle-pg93x64-2.1.1)

I'm I missing something?
thanks,
cheers,
Rémi-C

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Paul Ramsey | 12 Nov 17:09 2014
Picon

Re: FileGDB curves

Correct, the curves were likely ruined on the way through GDAL. In
some ways, fortunate they ever got through at all.

On Wed, Nov 12, 2014 at 7:48 AM, Bborie Park <dustymugs <at> gmail.com> wrote:
> If I remember correctly, curve geometries are not currently supported in
> GDAL/OGR. There is a GDAL RFC making the rounds for adding curve support...
>
> http://trac.osgeo.org/gdal/wiki/rfc49_curve_geometries
>
> -bborie
>
> On Wed, Nov 12, 2014 at 7:44 AM, Andy Colson <andy <at> squeakycode.net> wrote:
>>
>> Shoot.  I changed the command to:
>>
>> ogr2ogr
>>   -f PostgreSQL
>>   -t_srs EPSG:3857
>>   'PG:dbname=gis'
>>   WCWebDataCC.gdb
>>   -lco FID=gid
>>   -lco SPATIAL_INDEX=OFF
>>   -lco GEOMETRY_NAME=the_geom
>>   -lco SCHEMA=washingtonmn
>>   -lco PRECISION=NO
>>   streets
>>
>>
>> (removing the -nlt) and I still get a MULTILINESTRING, and it still looks
>> the same.
>>
>> The ogr2ogr is part of a perl script, and the first command I posted was
>> my guess at what it generated, which was a little off.  The command above is
>> correct.
>>
>> I'm not sure what else to try.
>>
>>
>> -Andy
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users <at> lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Andy Colson | 12 Nov 00:00 2014
Picon

FileGDB curves

Hi All,

Not sure where the best place to ask this, so I'll start here.

I have a FileGDB that contains some curved lines, I import it into 
PostGIS and they come out as chopped off Multiline.

Here are some screen shots to help explain:

Here is arc viewing the .gdb:
http://testmaps.camavision.com/arc.png

Here is qgis viewing PostGIS:
http://testmaps.camavision.com/qgis.png

I tried to get arcCatalog to export to shapefile, but something went bad 
and I cannot even look at the .shp files.

Any hints how I can get the curve back?

Thanks for your time,

-Andy
Jerry Locke | 10 Nov 22:09 2014

Trouble installing PostGIS using MacOSx

Background

-----------------------------

OS version: Mac OS X 10.9.5

Postgres: 9.2.5_1 via Homebrew

PostGis: 2.1.4_1 via Homebrew

-----------------------------


Competed installation and am stuck with the following error when running

extension creation -


-- Enable PostGIS (includes raster)

CREATE EXTENSION postgis;

-- Enable Topology

CREATE EXTENSION postgis_topology;

-- fuzzy matching needed for Tiger

CREATE EXTENSION fuzzystrmatch;

-- Enable US Tiger Geocoder

CREATE EXTENSION postgis_tiger_geocoder;


OUTPUT

----------------------------------------


ERROR:  could not access file "$libdir/postgis-2.1": No such file or

directory

********** Error **********


ERROR: could not access file "$libdir/postgis-2.1": No such file or

directory

SQL state: 58P01

----------------------------------------


Thanks- Jerry

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Gmane