Re: [postgis-users] https://postgis.net/docs/geometry_distance_knn.html and index usage
es in some way. > > Have also tested with casting geography when creating table g1 and g2 and > that did not make any difference. > > Tested also with big workmem (1000 MB ) and the result was the same. > > I am running on POSTGIS="3.3.0dev 3.1.0alpha2-1532-gc8eedf3ae" > [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" PROJ="7.2.1" > LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 > (Internal)" TOPOLOGY > (1 row) > > Thanks. > > Lars > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > -- [image: Ausvet Logo] <https://www.ausvet.com.au/> Dr Ben Madin BVMS MVPHMgmt PhD MANZCVS GAICD Managing Director Mobile: +61 448 887 220 <+61448887220> E-mail: b...@ausvet.com.au Website: www.ausvet.com.au Skype: benmadin Address: 5 Shuffrey Street Fremantle, WA 6160 Australia ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] problem trying to compile postgis 3.2.1 on Mac arm64
Hi all, sorry to trouble you, but I'm trying to upgrade PostGIS 3.1.4 to 3.2.1 on Apple Silicon and getting the error: 'Undefined symbols for architecture arm64:' - the point in the build where it doesn't work is below (some of it), and it seems to relate to the "FlatGeoBuf" lib. I'm wondering if I have missed a particular configuration issue, I have also included the output of the ./configure file *Configure output* PostGIS is now configured for arm-apple-darwin21.4.0 -- Compiler Info - C compiler: gcc -std=gnu99 -g -O2 -fno-math-errno -fno-signed-zeros -Wall C++ compiler (FlatGeobuf): gcc -std=c++11 -x c++ CPPFLAGS: -I/usr/local/include -I/usr/local/include/libxml2 -I/usr/local/include/json-c -I/usr/local/include -DNDEBUG LDFLAGS: -lm SQL preprocessor: /usr/bin/cpp -traditional-cpp -w -P -- Additional Info - Interrupt Tests: DISABLED use: --with-interrupt-tests to enable -- Dependencies -- GEOS config: /usr/local/bin/geos-config GEOS version: 3.10.2 GDAL config: /Library/Frameworks/GDAL.framework/Programs/gdal-config GDAL version: 3.4.3 PostgreSQL config:/usr/local/pgsql14.2/bin/pg_config PostgreSQL version: PostgreSQL 14.2-av PROJ4 version:81 Libxml2 config: /usr/local/bin/xml2-config Libxml2 version: 2.9.10 JSON-C support: yes protobuf support: no PCRE support: Version 1 Perl: /usr/bin/perl --- Extensions --- PostGIS Raster: enabled PostGIS Topology: enabled SFCGAL support: disabled Address Standardizer support: enabled Documentation Generation xsltproc: /usr/local/bin/xsltproc xsl style sheets: dblatex: convert: mathml2.dtd: http://www.w3.org/Math/DTD/mathml2/mathml2.dtd *Output from the make script:* gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Werror=unguarded-availability-new -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -Wno-compound-token-split-by-macro -O2 -bundle -multiply_defined suppress -o postgis-3.so postgis_module.o lwgeom_accum.o lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o lwgeom_inout.o lwgeom_functions_basic.o lwgeom_btree.o lwgeom_box.o lwgeom_box3d.o lwgeom_geos.o lwgeom_geos_prepared.o lwgeom_geos_clean.o lwgeom_geos_relatematch.o lwgeom_generate_grid.o lwgeom_export.o lwgeom_in_gml.o lwgeom_in_kml.o lwgeom_in_geohash.o lwgeom_in_geojson.o lwgeom_in_encoded_polyline.o lwgeom_triggers.o lwgeom_dump.o lwgeom_dumppoints.o lwgeom_functions_lrs.o lwgeom_functions_temporal.o lwgeom_rectree.o long_xact.o lwgeom_sqlmm.o lwgeom_rtree.o lwgeom_transform.o lwgeom_window.o gserialized_typmod.o gserialized_gist_2d.o gserialized_gist_nd.o gserialized_supportfn.o gserialized_spgist_2d.o gserialized_spgist_3d.o gserialized_spgist_nd.o brin_2d.o brin_nd.o brin_common.o gserialized_estimate.o geography_inout.o geography_btree.o geography_centroid.o geography_measurement.o geography_measurement_trees.o geometry_inout.o postgis_libprotobuf.o mvt.o lwgeom_out_mvt.o geobuf.o lwgeom_out_geobuf.o lwgeom_out_geojson.o flatgeobuf.o lwgeom_in_flatgeobuf.o lwgeom_out_flatgeobuf.o postgis_legacy.o -lm ../deps/flatgeobuf/libflatgeobuf.la ../libpgcommon/libpgcommon.a ../liblwgeom/.libs/liblwgeom.a -L/usr/local/lib -lgeos_c -lproj -L/usr/local/lib -ljson-c -L/usr/local/lib -lxml2 -lz -lpthread -liconv -lm-lm -bundle_loader /usr/local/pgsql14.2/bin/postgres ld: warning: dylib (/usr/local/lib/libjson-c.dylib) was built for newer macOS version (12.1) than being linked (12.0) Undefined symbols for architecture arm64: "std::__1::__shared_weak_count::__get_deleter(std::type_info const&) const", referenced from: vtable for std::__1::__shared_ptr_emplace > in libflatgeobuf.la(flatgeobuf_c.o) "std::__1::__vector_base_common::__throw_length_error() const", referenced from: std::__1::__vector_base, std::__1::allocator > >::__throw_length_error() const in libflatgeobuf.la(flatgeobuf_c.o) cheers Ben -- [image: Ausvet Logo] <https://www.ausvet.com.au/> Dr Ben Madin BVMS MVPHMgmt PhD MANZCVS GAICD Managing Director Mobile: +61 448 887 220 <+61448887220> E-mail: b...@ausvet.com.au Website: www.ausvet.com.au Skype: benmadin Address: 5 Shuffrey Street Fremantle, WA 6160 Australia ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Role of PostgreSQL and PostGIS in disease outbreak control
Hi all, This paper: https://doi.org/10.1016/j.prevetmed.2018.10.009 discusses our solution which powers the national animal health system in Indonesia and Australia. It is heavily based on PostgreSQL / postgis as disease control is about many things, but most definitely when and where If anyone would like a copy, I can send one on, but can't attach to this listserv.The abstract is below: Epidemiology provides insights about causes of diseases and how to control them, and is powered by surveil- lance information. Animal health surveillance systems typically have been designed to meet high-level gov- ernment informational needs, and any incentives for those who generate data (such as animal owners and animal health workers) to report surveillance information are sometimes outweighed by the negative consequences of reporting; underreporting is a serious constraint. This problem can persist even when modern advances in in- formation and communications technology (ICT) are incorporated into the structure and operation of surveil- lance systems, although some problems typical of paper-based systems (including timeliness of reporting and response, accuracy of data entry, and level of detail recorded) are reduced. On occasions, however, additional problems including sustainability arise. We describe two examples of a philosophical approach and ICT platform for the development of powerful and sustainable health information systems that are people-centred and do not exhibit these typical problems. iSIKHNAS is Indonesia's integrated animal health information system, and PIISAC is a sustainable secure research platform based on full production data from participating commercial Chilean aquaculture companies. Epidemiologists working with these systems are faced with interesting new challenges, including the need to develop skills in extracting appropriate surveillance outcomes from large volumes of continually-streaming data. cheers Ben On Fri, 14 Aug 2020 at 19:57, Shaozhong SHI wrote: > Dear All Friends, > > We are looking for reference materials such as case studies in application > of PostgreSQL/PostGIS in disease outbreak control. > > This is an excellent opportunity for demonstrating the usefulness of the > technology and techniques for this. > > Should anyone come across excellent literature on this topic, please let > me know. > > I will find opportunities for promoting the excellence of > PostgeSQL/PostGIS community. > > Looking forward to hearing from anyone who knows. > > Regards, > > Shao > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users -- [image: Ausvet Logo] <https://www.ausvet.com.au/> Dr Ben Madin BVMS MVPHMgmt PhD MANZCVS GAICD Managing Director Mobile: +61 448 887 220 <+61448887220> E-mail: b...@ausvet.com.au Website: www.ausvet.com.au Skype: benmadin Address: 5 Shuffrey Street Fremantle, WA 6160 Australia ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS generic dumper/loader
Was there a specific use case, or reason that those weren't adequate? cheers Ben On Tue, 25 Feb 2020 at 04:05, Diego Vargas wrote: > Hi all, > > Are there any alternatives for the pgsql2shp / shp2pgsql for dumping / > loading gis data? > > > Thanks! > > Diego > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users -- [image: Ausvet Logo] <https://www.ausvet.com.au/> Dr Ben Madin BVMS MVPHMgmt PhD MANZCVS GAICD Managing Director Mobile: +61 448 887 220 <+61448887220> E-mail: b...@ausvet.com.au Website: www.ausvet.com.au Skype: benmadin Address: 5 Shuffrey Street Fremantle, WA 6160 Australia ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Order of linestrings when using st_linemerge
G'day all,
I have an example where I have a large number of multilinestrings
representing the seaway track from one port to another. In order to
interpolate the position of a vessel (this is historical data) along these
tracks, we used a combination of st_linemerge and st_interpolate.
The multilinestring respects the direction of travel (from the origin to
the destination), so we thought this approach would work.
However, to use st_interpolate, we need linestrings, so we used linemerge
(looks perfect) but the line now goes from the destination to the origin.
(of course, this is not predictable - sometimes it is in the right order).
Is there any way to 'force' the linestring to have the same ordering as the
multi-line string? we are using linestringm, can we take advantage of the M
value?
example:
SELECT
st_asewkt(st_linemerge(st_geomfromewkt('SRID=4326;MULTILINESTRINGM((115.723248216579
-32.0454874969596 1,115.356718360803 -31.2649393614626
1.47593019400197,114.996206563468 -30.4833681123784
1.94793716099846,114.641442213985 -29.70082100447
2.41616662431401,114.292166370259 -28.9173431382814
2.88076010193012,113.948131063039 -28.1329775961666
3.34185510703625,113.609098642971 -27.347765569158
3.79958533994624,113.274841167352 -26.5617464753925
4.25408087178885,112.945139823841 -25.7749580707562
4.70546832037234),(112.945139823841 -25.7749580707562
4.70546832037234,112.569944851044 -24.955984154436
5.17822970085566,112.199711533849 -24.1360828251298
5.64785480680645,111.834198632833 -23.3153001472376
6.11447655756696,111.473173947045 -22.493680351942
6.57822465026799,111.116413762924 -21.6712659546459
7.03922572419879,110.763702333863 -20.8480978652059
7.49760351897373,110.414831388289 -20.0242154915183
7.95347902680775,110.069599664243 -19.1996568369764
8.40697063920594,109.727812468601 -18.3744585922724
8.85819428836509,109.389281259168 -17.5486562219864
9.30726358357774,109.053823248005 -16.7222840463694
9.75428994291997,108.721261024435 -15.8953753186967
10.1993827204972,108.391422196296 -15.0679622985443
10.6426493295126,108.06413904806 -14.2400763213105
11.0841953614157,107.73924821455 -13.411747864288
11.524124701379,107.416590369051 -12.5830066095668
11.9625396403441,107.096009924666 -11.7538815040321
12.3995409838705,106.777354747869 -10.9244008167035
12.8352281580124,106.46047588321 -10.0945921936453
13.2696993124424,106.145227288244 -9.2644827106659
13.7030514210349,105.831465577744 -8.43409892401077
14.1353803801144,105.519049776353 -7.60346691924052
14.5667811045696,105.207841078825 -6.77261235847855
14.997347622028),(105.990650130672 -5.91167003050344
14.997347622028,105.599572459125 -6.34228797455069
15.2785767014246,105.207841078825 -6.77261235847855
15.5600407259128),(106.663050248418 -5.99905605734687
15.5600407259128,105.990650130672 -5.91167003050344
15.8868531270388),(106.881437549142 -6.08393762734487
15.8868531270388,106.709016406789 -6.01184678072196
15.9769961743295),(106.709016406789 -6.01184678072196
15.9769961743295,106.663050248418 -5.99905605734687 16))')));
cheers
Ben
--
Ben Madin
BVMS PhD
m : +61 448 887 220
e : b...@ausvet.com.au
5 Shuffrey Street, Fremantle
Western Australia
on the web: www.ausvet.com.au
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete
it and notify the sender. The contents of this email are the opinion of the
writer only and are not endorsed by Ausvet unless expressly stated
otherwise. Although Ausvet uses virus scanning software we do not accept
liability for viruses or similar in any attachments.
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] TR: strange behavior of ST_Area(geography)
Works for me ... from the small sample size, it would appear that the version of GDAL you have compiled against (I am using 2.1.3, as is Olivier) might need to be updated from the 2013 release. hth Ben On 16 October 2017 at 23:48, Paul Ramsey wrote: > Werks fer me also: > > st_equals | eq_area | geog1 | geog2 | geog3 | geom > ---+-+---+---+---+-- > t | t | t | t | t | t > > > On Sun, Oct 15, 2017 at 6:07 AM, IIDA Tetsushi wrote: > >> thank you, Olivier. >> >> sorry, i forgot to describe my environment. >> >> PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian >> 4.9.2-10) 4.9.2, 64-bit >> POSTGIS="2.3.2 r15302" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. >> 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, >> released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER >> >> (docker image "mdillon/postgis") >> >> >> 2017-10-15 21:34 GMT+09:00 Olivier Leprêtre : >> > 9.6 and >> > "POSTGIS="2.3.2 r15302" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. >> 4.9.1, 04 March 2015" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.7.8" >> LIBJSON="0.12" TOPOLOGY RASTER" >> > >> > -Message d'origine- >> > De : Olivier Leprêtre [mailto:o.lepre...@gmail.com] >> > Envoyé : dimanche 15 octobre 2017 14:33 >> > À : 'PostGIS Users Discussion' >> > Objet : RE: [postgis-users] strange behavior of ST_Area(geography) >> > >> > I tested your query with 9.6 all tests return true. >> > >> > Olivier >> > >> > -Message d'origine- >> > De : postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] De >> la part de IIDA Tetsushi Envoyé : dimanche 15 octobre 2017 13:42 À : >> postgis-users@lists.osgeo.org Objet : [postgis-users] strange behavior >> of ST_Area(geography) >> > >> > Hi, >> > >> > These polygons have the same shape but orientation. >> > >> > --- my query - >> > WITH poly AS ( >> > SELECT >> > ST_SetSRID(ST_GeomFromText( >> >'POLYGON((138.002 37.001, 138.003 37.0010, 138.003 37.0013, 138.002 >> 37.001))' >> >),4326) g1, >> > ST_SetSRID(ST_GeomFromText( >> >'POLYGON((138.002 37.001, 138.003 37.0013, 138.003 37.0010, 138.002 >> 37.001))' >> >),4326) g2 >> > ) >> > SELECT ST_Equals(g1,g2), >> > ST_Area(g1) = ST_Area(g2) AS eq_area, >> > ST_Area(g1::geography) = ST_Area(g2::geography) AS geog1, >> > ST_Area(g1::geography, false) = ST_Area(g2::geography, false) AS >> geog2, >> > ST_Area(g1::geography) = ST_Area(ST_Reverse(g2)::geography) >> AS geog3, >> > ST_Area(ST_Transform(g1,32654)) = ST_Area(ST_Transform(g2,32654)) AS >> geom FROM poly; >> > >> > - output - >> > >> > st_equals | eq_area | geog1 | geog2 | geog3 | geom >> > ---+-+---+---+---+-- >> > t | t | f | t | t | t >> > >> > >> > I expected all these tests return true. >> > Is this behavior by spec? >> > >> > Thanks >> > >> > -- >> > hogeman >> > ___ >> > postgis-users mailing list >> > postgis-users@lists.osgeo.org >> > https://lists.osgeo.org/mailman/listinfo/postgis-users >> > >> > >> > --- >> > L'absence de virus dans ce courrier électronique a été vérifiée par le >> logiciel antivirus Avast. >> > https://www.avast.com/antivirus >> > >> > ___ >> > postgis-users mailing list >> > postgis-users@lists.osgeo.org >> > https://lists.osgeo.org/mailman/listinfo/postgis-users >> >> >> >> -- >> Iida, Tetsushi >> 飯田 哲士 >> ___ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > -- Dr Ben Madin Managing Director m : +61 448 887 220 e : b...@ausvet.com.au 5 Shuffrey Street, Fremantle Western Australia on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments. ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Create trigger latitude and longitude
Rafael, I'd probably question why you need to do this, but it is just a routine INSERT/UPDATE AFTER trigger function. The answers you seek are probably covered in https://www.postgresql.org/docs/9.6/static/triggers.html I'm presuming you know how to get the x and y coordinates for a point... cheers Ben On 12 September 2017 at 17:48, Rafael Reis wrote: > Hi, > > I have a table of point type, and whenever i insert a point I want > latitude and longitude (EPSG:4326) to appear in a text field of this table, > in decimal degrees. Can anyone tell me how I should create a trigger for > this? > > Thanks, > > Rafael > > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > -- Dr Ben Madin Managing Director m : +61 448 887 220 t : +61 8 9336 5022 e : b...@ausvet.com.au Level 2 / 10 High Street, Fremantle Western Australia on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS export - data is inexplicably truncated from 3234 records to 3221 when using 'pgsql2shp', PGAdmin, AND QGIS 2.6+
ghton (on windows...we've tested using more up-to-date >> versions too) >> >> Here is the data <https://spaces.hightail.com/space/ihoE7> (2 MB >> unzipped on a cloud drive). >> >> Here is what I do... >> >> 1) Load the shapefile into Postgres using the PGAdmin tool (works >> fine...notably it is loaded as a MULTIPOLYGON[2] EVEN THOUGH the >> Shapefile type is Polygon) >> >> *3234 record counts* >> >> Importing with configuration: as_clean_test_ogr_export_utf8, public, >> geom, H:\as_clean_test_ogr_export_utf8.shp, mode=c, dump=1, simple=0, >> geography=0, index=1, shape=1, srid=4326 Shapefile type: Polygon >> PostGIS type: MULTIPOLYGON[2] Shapefile import completed. >> Connecting: host=pgsqlgis-repos port=5432 user=local-dev >> password='*' >> >> 2) Try to export the table tp shapefile... (this doesn't work for the >> following..and notably they are all trying to export it as a Polygon) >> >> UPON Export (using >pgsql2shp) >> >> H:\>pgsql2shp -f E:/as_clean_test_ogr_export_utf8.shp -h -u >> -p -P >> "as_clean_test_ogr_export_utf8" >> >> Initializing... >> Done (postgis major version: 2). >> Output shape: Polygon >> Dumping: X*[3221 rows]*. >> >> QGIS (Save As in QGIS 2.6.0) >> >> Export to vector file failed. >> Error: Feature write errors: >> Feature geometry not imported (OGR error: ) Feature geometry not >> imported (OGR error: ) Feature geometry not imported (OGR error: ) >> Feature geometry not imported (OGR error: ) Feature geometry not >> imported (OGR error: ) Feature geometry not imported (OGR error: ) >> Feature geometry not imported (OGR error: ) Feature geometry not >> imported (OGR error: ) Feature geometry not imported (OGR error: ) >> Feature geometry not imported (OGR error: ) Feature geometry not >> imported (OGR error: ) Feature geometry not imported (OGR error: ) >> Feature geometry not imported (OGR error: ) *Only 3221 of 3234 >> features written.* >> >> Export Using PostGIS 2.0 Shapefile and DBF Loader Exporter >> >> *3221 record counts* >> >> Exporting with configuration: as_clean_test_ogr_export_utf8, public, >> as_clean_test_ogr_export_utf8 >> Done (postgis major version: 2) >> Output shape: Polygon >> Table export completed. >> >> >> >> >> ___ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> http://lists.osgeo.org/mailman/listinfo/postgis-users >> > > J. Gustavo > -- > Jorge Gustavo Rocha > Departamento de Inform tica > Universidade do Minho > 4710-057 Braga > Tel: +351 253604480 > Fax: +351 253604471 > M vel: +351 910333888 > skype: nabocudnosor > ___ > 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 -- Ben Madin m : +61 448 887 220 t : +61 8 9336 5022 e : b...@ausvet.com.au 10 High Street, Fremantle Western Australia find us: http://w3w.co/duke.basket.decks on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Question on performance probably related to detoast and/or bboxes
Thanks Regina, even if it doesn't help Stephen all that much, it is helpful to me ! cheers Ben On 19 November 2016 at 19:44, Regina Obe wrote: > > > > Hi Regina, > > On 11/18/2016 3:28 AM, Regina Obe wrote: > > Since you are doing 0.0, you might be better off using ST_Intersects > > So > > st_dwithin(pnt, a.geom, 0.0) > > change to: > > ST_Intersects(pnt,a.geom) > > > Did this change recently? I was under the impression that st_dwithin was > faster than st_intersects? Anyway, this is good to know, Thanks! > > > > No it didn't, > ST_Intersects has generally always been faster. In most cases > ST_Intersects is faster than ST_DWithin since it can take advantage of > certain topological features of a geometry, so it not quite as sensitive to > the number of points as ST_DWithin is. It also supports prepared > geometry. Paul can correct me, but I don't think we've added prepared > geometry support for ST_DWithin. That means it should perform much better > when comparing a biggo single geometry against a lot of different > geometries. > > > > ST_DWithin was much slower until Nicklas made some major improvements a > while back which increased the speed I think 10 fold, making it in many > cases almost as fast and sometimes faster than ST_Intersects. > > > > That said the main benefit of ST_Dwithin is > > > > 1) You can provide a tolerance which you can't with ST_Intersects, > and it will short-circuit if it hits that tolerance (doesn't need to > compute full distance) > > 2) It doesn't care if your geometry is invalid, where as > ST_Intersects will often throw up its hands and throw false in your face > regardless how close the geometries are because the DEIM it relies on needs > the geometries to be valid. > > > > Hope that helps, > > Regina > > http://www.postgis.us > > http://postgis.net > > > > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/postgis-users > -- Ben Madin m : +61 448 887 220 t : +61 8 9336 5022 e : b...@ausvet.com.au 10 High Street, Fremantle Western Australia find us: http://w3w.co/duke.basket.decks on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Problem extracting SQL Server Geometry (or, what is the 0x character?)
G'day all, I hope a simple case of something I've missed, but we are trying to extract data from a SQL Server database into PostGIS use tds_fdw... the data in SQL Server appears to be in WKB - but when when connect to this field we have a precursor 0x. I can't find any references to anyone else suffering this problem, but that could be because I'm trying a lazy approach to automate retrieval of hundreds of tables using the FDW (that's what it is for, right?) I'm left with a sense that it is an encoding error between the two systems? I've tried making the fdw column text instead of geometry, but I can't get rid of the 0x, and no amount of trying to cajole the text to any other form makes it any happier. To complicate it, for testing I'm going from SQL Server 2014 (running in Windows 8.1 in a VM) to PostgreSQL 9.4 on a Mac (El Capitan) using tds_fdw compiled on the same mac. POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r0" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.1.1, released 2016/07/07" LIBXML="2.7.8" LIBJSON="0.12.1" RASTER Any ideas gratefully received? cheers Ben m : +61 448 887 220 e : b...@ausvet.com.au 10 High Street, Fremantle Western Australia on the web: www.ausvet.com.au This transmission is for the intended for a mailing list and is clearly never going to be confidential information. If you have received this transmission in error, apologies! The contents of this email are the likely ill-educated opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Thanks for reading. An even bigger thanks for any help you can provide. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] issue loading shapefiles into postgis via batch
Rather than putting a password into a plain text file, we prefer to create a user with very limit priviliges - ie can only access one schema and can only create and insert into that schema and read nothing. Then in the pg_hba.conf give that user local access to your database using trust. In reality, whether you put in a plain text password or no password, it should only be for a user with limited priviliges, and no capacity to to access private data or damage the existing database. If you then limit their access from the local server, you are getting pretty secure. Cheers Ben On Friday, 26 August 2016, Mark Volz wrote: > Hello, > > > > I am having issues with a Windows batch load script. This script should > scan for any new shapefiles in the “PostGIS Incoming” folder, load the > shapefiles into postGIS, then place the shapefiles in a completed folder. > This script seemed to work in the past, but is now complaining about a > password – which I did not change. > > > > What is wrong with this script, and or what can I do to specify a password? > > > > ### > > # LOAD SHAPEFILES INTO POSTGIS SCRIPT > > ### > > > > set PATH=C:\Program Files\PostgreSQL\9.2\bin;%PATH% > > cd /d "D:\PostGISIncoming\LyonGIS" > > rem load data to postgis > > for %%f in (*.shp) do shp2pgsql -d -I -s 103749 -W LATIN1 %%f %%~nf | psql > -w -U postgres -d lyongis > > rem move data to done folder > > for %%a in (*.*) do move "%%a" "D:\PostGISIncoming\LyonGIS\Done" > > > > > > > > Thanks > > Sincerely, > > *Mark Volz, GISP* > > > -- Sent from my iPhone. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Query/View permission deny - strange behaviour
What do you mean: If I select it, both as pippo and as a member of sitarpa_admins, an error rises saying: ERROR: permission denied for relation comuni_fvg_2014_3004 How do you "select" it? Cheers Ben On Monday, 16 May 2016, Pietro Rossin wrote: > Hello, I'm going crazy with this problems... > > I'll report real names and statements > > *Here is the user login pippo * > ** > CREATE ROLE pippo LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE > NOREPLICATION; > GRANT sitarpa_catasto_scarichi_editors_ud TO pippo; > GRANT sitarpa_viewers TO pippo; > > > *Here is the db admin role group* > ** > CREATE ROLE sitarpa_admins NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE > NOREPLICATION; > GRANT sitarpa_basi_amministrative_admins TO sitarpa_admins; > GRANT sitarpa_catasto_scarichi_admins TO sitarpa_admins; > and some others > * > > *Here the role group sitarpa_catasto_scarichi_editors_ud:* > *** > CREATE ROLE sitarpa_catasto_scarichi_editors_ud > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; > ** > > *Here the role group sitarpa_viewers:* > > CREATE ROLE sitarpa_viewers > NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; > * > > *Here is the first schema called basi_amministrative* > > > CREATE SCHEMA basi_amministrative AUTHORIZATION sitarpa_admins; > > GRANT ALL ON SCHEMA basi_amministrative TO sitarpa_admins; > GRANT USAGE ON SCHEMA basi_amministrative TO > sitarpa_basi_amministrative_admins; > GRANT USAGE ON SCHEMA basi_amministrative TO > sitarpa_basi_amministrative_editors; > GRANT USAGE ON SCHEMA basi_amministrative TO > sitarpa_basi_amministrative_viewers; > GRANT USAGE ON SCHEMA basi_amministrative TO sitarpa_viewers; > GRANT USAGE ON SCHEMA basi_amministrative TO pippo; > > > ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON > TABLES > TO sitarpa_basi_amministrative_admins; > > ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative > GRANT INSERT, SELECT, UPDATE, DELETE, TRIGGER ON TABLES > TO sitarpa_basi_amministrative_editors; > > ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative > GRANT SELECT ON TABLES > TO sitarpa_basi_amministrative_viewers; > > ALTER DEFAULT PRIVILEGES IN SCHEMA basi_amministrative > GRANT USAGE ON SEQUENCES > TO sitarpa_basi_amministrative_editors; > *** > > *Here is the schema catasto_scarichi:* > * > CREATE SCHEMA catasto_scarichi AUTHORIZATION sitarpa_admins; > > GRANT ALL ON SCHEMA catasto_scarichi TO sitarpa_admins; > GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_admins; > GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_editors; > GRANT USAGE ON SCHEMA catasto_scarichi TO sitarpa_catasto_scarichi_viewers; > GRANT USAGE ON SCHEMA catasto_scarichi TO > sitarpa_catasto_scarichi_editors_ud; > > ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi > GRANT SELECT ON TABLES > TO sitarpa_catasto_scarichi_viewers; > > ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON > TABLES > TO sitarpa_catasto_scarichi_admins; > > ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi > GRANT INSERT, SELECT, UPDATE, DELETE, TRIGGER ON TABLES > TO sitarpa_catasto_scarichi_editors; > > ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi > GRANT SELECT, UPDATE, USAGE ON SEQUENCES > TO sitarpa_catasto_scarichi_admins; > > ALTER DEFAULT PRIVILEGES IN SCHEMA catasto_scarichi > GRANT USAGE ON SEQUENCES > TO sitarpa_catasto_scarichi_editors; > *** > > > *Here is the first table in the problemathic view:* > > * > CREATE TABLE basi_amministrative.comuni_fvg_2014_3004 > ( > id serial NOT NULL, > geom geometry(MultiPolygonZ,3004), > nome character varying(255), > cod_istat character varying(255), > CONSTRAINT comuni_fvg_2014_3004_pkey PRIMARY KEY (id) > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE basi_amministrative.comuni_fvg_2014_3004 OWNER TO > sitarpa_basi_amministrative_admins; > GRANT ALL ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO > sitarpa_basi_amministrative_admins; > GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE > basi_amministrative.comuni_fvg_2014_3004 TO > sitarpa_basi_amministrative_editors; > GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO > sitarpa_basi_amministrative_viewers; > GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO > sitarpa_viewers; > GRANT SELECT ON TABLE basi_amministrative.comuni_fvg_2014_3004 TO pippo; > > -- Index: basi_amministrative.sidx_comuni_fvg_2014_3004_geom > > -- DROP INDEX basi_amministrative.sidx_comuni_fvg_2014_3004_geom; > > CREATE INDE
Re: [postgis-users] Postgis doesn't work after search_path
Hi,
I don't want to burst your bubble, but I think it would pay to take a
little bit longer to work out what the search_path means and why it didn't
work. It will save you great pain and tears in the future.
cheers
Ben
On 4 August 2015 at 16:07, franco base wrote:
> Hi Sandro,
> Your select returns public (8 rows).
> But i just solved restarting the server.
>
> Thanks a lot.
>
> FB
>
>
>
>
> 2015-08-04 9:56 GMT+02:00 Sandro Santilli :
>
>> On Tue, Aug 04, 2015 at 09:21:56AM +0200, franco base wrote:
>> > Postgis is in Public Schema
>> >
>> > I run
>> > ALTER DATABASE mydb SET search_path TO 'test'
>> >
>> > After Postgis doesn't work
>>
>> This is expected.
>>
>> > So I give this command
>> > reset search_path
>> >
>> > and then
>> > alter database mydb set search_path = "$user", public, topology
>> >
>> > This select:
>> > SELECT r.rolname, d.datname, rs.setconfig
>> > FROM pg_db_role_setting rs
>> > LEFT JOIN pg_roles r ON r.oid = rs.setrole
>> > LEFT JOIN pg_database d ON d.oid = rs.setdatabase
>> >
>> > show that mydb is now ok and the setconfig is the same for all the db:
>> > "{"search_path=\"$user\", public, topology"}"
>> >
>> >
>> > On the other db Postgis is ok but on 'mydb' postgis doesn't work again.
>> > This is the error (it's the same for all postgis function):
>> >
>> > ERROR: function st_union(public.geometry) does not exist
>> > LINE 33: select st_union(wkb_geometry) AS wkb_geometry
>> >
>> > Have you any tips?
>>
>> show search_path;
>> select n.nspname from pg_namespace n, pg_proc p where p.proname =
>> 'st_union' and n.oid = p.pronamespace;
>>
>> --strk;
>>
>> () Free GIS & Flash consultant/developer
>> /\ http://strk.keybit.net/services.html
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
--
Ben Madin
BVMS PhD, MANZCVS
t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au
AusVet Animal Health Services
Western Australia
www.ausvet.com.au
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Fw: Mapserver displays an empty map using layer from postgis
Your bounding box in the uri (5,97,21,106): a) is invalid / unrealistic for WGS84 coordinates, and / or b) doesn't / wouldn't include your layer extent -17.9334 30.5019 -17.7303 30.8789 hth On 6 March 2015 at 16:04, Ronald Muchini wrote: > > > --- On Fri, 3/6/15, Ronald Muchini wrote: > > > From: Ronald Muchini > > Subject: Mapserver displays an empty map using layer from postgis > > To: "ms4w_users" > > Cc: "ms4w_users" > > Date: Friday, March 6, 2015, 12:02 AM > > I have a raster layer in postgis, > > which I'm trying to display it in the browser using the > > following code. My Postgis is Installed in the directory, > > "C:\Program Files(x86)\PostgreSQL\9.3\bin" and mapsever > > directory is "C:\ms4w". > > > > > > MAP > > NAME "Lakes" > > IMAGECOLOR 255 255 255 > > SIZE 600 800 > > IMAGETYPE PNG24 > > PROJECTION > > "init=epsg:4326" > > END > > EXTENT -17.9334 30.5019 -17.7303 30.8789 > > WEB > > METADATA > > "ows_enable_request" > > "*" > > "map" > > "C:/ms4w/apps/Lakes/configpg.map" > > "ows_schemas_location" > > "http://schemas.opengeospatial.net"; > > "ows_title" > > "LakesWMS" > > "ows_onlineresource" > > " > http://localhost/cgi-bin/mapserv.exe?map=C:/ms4w/apps/Lakes/configpg.map&"; > > "ows_srs" "EPSG:4326 " > > #latlon > > > > "wms_feature_info_mime_type" "text/plain" > > > > "wms_feature_info_mime_type" "text/html" > > "wms_server_version" > > "1.3.0" > > "wms_formatlist" > > "image/png,image/gif,image/jpeg" > > "wms_format" > > "image/png" > > END #metadata > > END #web > > LAYER > > NAME "ras" > > TYPE RASTER > > STATUS ON > > CONNECTIONTYPE postgis > > CONNECTION "user=postgres password=magodo > > dbname=wqm host=localhost port=5432 options='-c > > client_encoding=UTF8'" > > DATA "geometry FROM testras9 USING UNIQUE > > id USING srid=4326" > > METADATA > > "ows_title" "ras" > > "ows_extent" " > > -17.9334 30.5019 -17.7303 30.8789" > > END #metadata > > PROJECTION > > "init=epsg:4326" > > END > > CLASS > > NAME "ras" > > OUTLINECOLOR 255 255 > > 255 > > COLOR 137 205 102 > > END #class ras > > END #layer ras > > END #map > > > > > > > > Im calling the webpage using the url below: > > " > http://localhost/cgi-bin/mapserv.exe?map=C:/ms4w/apps/Lakes/config.map&SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&LAYERS=ras&STYLES=&CRS=EPSG:4326&BBOX=5,97,21,106&WIDTH=600&HEIGHT=800&FORMAT=image/png > " > > > > > > No errors pop up but the browser displays an empty map > > space. > > How can I resolve this issue > > > > Thanks in advance > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > -- Ben Madin BVMS PhD, MANZCVS t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services Western Australia www.ausvet.com.au ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Possible for ST_Intersects to not include the perimeter?
For two cents, a quick but inaccurate solution might be to shrink your polygon slightly by using st_buffer() with a negative radius? Ben On 22 January 2015 at 16:50, toni hernández wrote: > Joseph, > > With St_Relate (http://postgis.net/docs/ST_Relate.html) we can get all > kind of relations between geometries. > You can get boundary intersections, interior intersections, interior > intersections without boundary intersections all possibilities are > supported. > > St_Relate uses the DE-91M model to define this relations between > geometries. > To know more about DE-91M: http://en.wikipedia.org/wiki/DE-9IM > > > > http://postgis.net/docs/using_postgis_dbmanagement.html#DE-9IM > > > On 21/01/2015 22:22, Joseph Spenner wrote: > > I have a polygon as an input, and I'm trying to find all polygons which > share points, but not the perimiter points themselves. Is this possible? > > Real application: > I have NWS polygons describing Thunderstorm Warnings, which are made up > of counties within the state. Some of those counties might be on the edge > of the state. I want to query my database to show me all Warnings which > are in a supplied state. However, if I query the *adjacent* state which > borders the counties from the first state, I get those Warnings because > they share the same parimeter points. > > Is there a way to construct a query which will not return anything if > the only points in common are the perimeter values themselves? > > I tried a few variations on > ST_Intersects/ST_Covers/ST_Contains/ST_Overlaps, supplying various AND/OR > and TRUE/FALSE combinatins, but I can't seem to get the right combo to > accomplish my task. > > Any help would be great. > > Thanks! > > Regards, > Joseph Spenner > > > > > ___ > postgis-users mailing > listpostgis-users@lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > _______ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > -- Ben Madin BVMS PhD, MANZCVS t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services Western Australia www.ausvet.com.au ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Trouble installing PostGIS using MacOSx
I don't know much about Homebrew, but does it work using either of: a) William Kyngesbury's installers? (v.v. easy) b) install from source? (also very easy in this release on Yosemite, but shouldn't be so different on Mavericks?) cheers Ben On 11 November 2014 05:09, Jerry Locke wrote: > 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@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > -- Ben Madin BVMS PhD, MANZCVS t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services Western Australia www.ausvet.com.au ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] export
If you mean from one postgis database to another postgis database I think you will find pg_dump most helpful using the -t flag for the table you want. If they are both on the same cluster: pg_dump -t myTable myDatabase1 | psql myDatabase2 (caveat is make sure that the second database has postgis extension installed first) Otherwise, dump to a file pg_dump -t myTable myDatabase1 | gzip -9 > myTable.sql.gz unzip and reload into second database. cheers Ben On 14 October 2014 00:45, Stephen Crawford wrote: > Hello All, > > I want to copy a postgis table from one database to another. Is dumping > to a shapefile the best way to do this? Any other method? Pros and cons? > > Thanks, > Steve > > -- > Stephen Crawford > Center for Environmental Informatics > The Pennsylvania State University > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > -- Ben Madin BVMS PhD, MANZCVS t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services Western Australia www.ausvet.com.au ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] postgis_full_version error
G'day Regina, You have nailed it, it was a database from an old (postgis 1.5) system - so I actually expected to have many more errors. Luckily it was so old that I had installed postgis into its own schema, so a simple DROP SCHEMA gis CASCADE; has solved this problem. I have 'noticed' this since upgrading a number of systems : > NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed? We don't always need / use topology, so don't install it... is this notice appearing a new behaviour, or have I messed up something else? Thanks again, cheers Ben On 10 October 2014 05:31, Paragon Corporation wrote: > Ben, > > That function hasn't existed for a while in PostGIS. I would say circa > 1.3. > I think it was a time when we were experimenting with using Java and JTS > natively in PostGIS. > > I suspect you have a mix of PostGIS installs. Did you install a backup > from > somewhere? > > If so, you might be able to fix the issue by > > Running postgis upgrade scripts. > > Hope that helps, > Regina > http://www.postgis.us > http://postgis.net > > > > > > > > -- > G'day all, > > I'm not sure if this is a postgis or an ubuntu issue, but having just > installed postgis on to ubuntu 14.04 from the pgdg repository, I am getting > this error : > > # select postgis_full_version(); > ERROR: function postgis_jts_version() does not exist > LINE 1: SELECT postgis_jts_version() > ^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > QUERY: SELECT postgis_jts_version() > CONTEXT: PL/pgSQL function postgis_full_version() line 15 at SQL > statement > > > I have previously installed onto many Ubuntu 12.04 without this problem. I > guess there are two parts: > > 1. do I have / need jts (it is showing geos 3.4.2-CAPI-1.8.2 r3921) ? > > 2. if I don't have it, should it fail completely, or just return NULL ? > > cheers > > Ben > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > -- Ben Madin ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] postgis_full_version error
G'day all, I'm not sure if this is a postgis or an ubuntu issue, but having just installed postgis on to ubuntu 14.04 from the pgdg repository, I am getting this error : # select postgis_full_version(); ERROR: function postgis_jts_version() does not exist LINE 1: SELECT postgis_jts_version() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT postgis_jts_version() CONTEXT: PL/pgSQL function postgis_full_version() line 15 at SQL statement I have previously installed onto many Ubuntu 12.04 without this problem. I guess there are two parts: 1. do I have / need jts (it is showing geos 3.4.2-CAPI-1.8.2 r3921) ? 2. if I don't have it, should it fail completely, or just return NULL ? cheers Ben -- Ben Madin ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Associate CopyTopology Schema with TopoGeometry
G’day, Can I ‘second’ this question! - I am trying to simplify complex geometries, and would love a way of duplicating them so I don’t have to rebuild the topological version every time I over-simplify… cheers Ben On 2014-01-04, at 06:07 , Matt Kenny wrote: > Hello, > > I've created a series of topology schemas, copied using the `CopyToplolgy` > function [1]. > > These schemas are not, by default, associated with any particular > layer/TopoGeometry. This can be seen in the contents of the layer table [2]. > > So, I'm wondering if it is possible to do one of the following: > - Create a new column of type topogeometry, and associate it with any of > these currently unassociated schemas. > - Modify an existing topogeometry column (in my example, > somalia_admin1s.topogeoms) > > Thanks, > Matt > > 1: http://postgis.net/docs/CopyTopology.html > 2: https://gist.github.com/mattmakesmaps/45fe32931cacdf40d107 > > -- > m a t t h e w k e n n y > http://mattmakesmaps.com > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Ben Madin t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services Western Australia AusVet's website: http://www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by AusVet Animal Health Services unless expressly stated otherwise. Although AusVet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Geoportal Server vs Postgres Geoportal
G’day Nicholas, We typically use a workflow like : 1. create a table with the desired geometry; 2. use pgsql2shp to dump it as a shapefile; 3. run shptree over it to create a .qix index (for mapserver and qgis, maybe not really necessary); 4. zip the files into an archive; 5. move them to a web accessible directory; 6. change the permissions to allow download. I’d note that we have recently started using ogr2ogr as pgsql2shp has been stopping about 5 records short of our typical full dataset (global first level administrative districts, with half of Zimbabwe missing!) ogr2ogr is a bit more complex, but can also output other file formats, not just shape file. You can also provide a query (not just a table name) but we don’t do it that way. cheers Ben On 2013-12-23, at 08:45 , Nicholas Tapia wrote: > If this is the wrong place to ask this question, please point me in the right > direction! > > I'm very new to databases and GIS. I'm researching geoportals and how they > offer geometries for download. > > As I understand it, Esri's "open source" Geoportal Server is the standard > method of offering data for download (besides offering shape files for > download as a file...like the census website). It is a software layer on top > of the database that allows you to select the geometries you want by drawing > a polygon. It also manages metadata and offers some search methods. > > But it doesn't allow me to make awesome sql queries. So I want to use > pgsql2shp to allow people to download the geometries. Are there any reasons > why I shouldn't offer geometry downloads from a postgres database using > pgsql2shp? > > Also, are there any examples of what I'm talking about now? Are there any > postgres dbs that allow for direct download of geometries? And don't use esri > geoportal server? > > Thanks! > > -Nicholas > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Ben Madin t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services Western Australia AusVet's website: http://www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by AusVet Animal Health Services unless expressly stated otherwise. Although AusVet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] pgsql2shp output truncated - work around
G’day again,
for lack of better solution I’ve found that ogr2ogr is working better - or at
least in it is returning all the rows:
ogr2ogr -f 'ESRI Shapefile' ${DIRPATH}${YEAR}/${MAPTYPE}bnda${YEAR}.shp
PG:'host=localhost user=ben dbname=maps password=whatever’
interestingly, it also appears to be working a little faster
cheers
Ben
On 2013-12-17, at 15:46 , Ben Madin wrote:
> G’day all,
>
> I’ve been using pgsql2shp to create shape files for a client by creating a
> table which contains global level 1 administrative boundaries (3670 records).
> When I connect to the db, I can see this many records and display the
> appropriate geometries.
>
> select ccode, admin1, mapcode, st_mem_size(geom), st_isvalidreason(geom) from
> polbnda2013 where ccode like 'ZWE' ORDER BY 1,2;
> ccode | admin1| mapcode | st_mem_size | st_isvalidreason
> ---+-+-+-+--
> ZWE | BULAWAYO|3230 |2496 | Valid Geometry
> ZWE | HARARE |3231 |3264 | Valid Geometry
> ZWE | MANICALAND |3232 | 108992 | Valid Geometry
> ZWE | MASHONALAND CENTRAL |3233 | 63616 | Valid Geometry
> ZWE | MASHONALAND EAST|3234 | 34080 | Valid Geometry
> ZWE | MASHONALAND WEST|3235 | 24432 | Valid Geometry
> ZWE | MASVINGO|3236 | 29840 | Valid Geometry
> ZWE | MATABELELAND NORTH |3237 | 28832 | Valid Geometry
> ZWE | MATABELELAND SOUTH |3238 | 69792 | Valid Geometry
> ZWE | MIDLANDS|3239 | 31296 | Valid Geometry
> (10 rows)
>
>
> When I run
>
> pgsql2shp -b -p 5432 -f outputs/2013/polbnda2013 -g geom -u ben -P noway
> oiemaps outputs.polbnda2013
> Initializing...
> Done (postgis major version: 2).
> Output shape: Polygon
> Dumping: X [3665 rows].
>
>
> The five rows missing are the last five rows in the table (Zimbabwe…).
>
> Ahh you say - an error in the data for Zimbabwe! But, if I just export
> Zimbabwe :
>
> pgsql2shp -b -r -p 5432 -f outputs/2013/polbnda2013 -g geom -u ben -P caitlin
> oiemaps "SELECT * FROM polbnda2013 WHERE ccode LIKE 'ZWE' ORDER BY ccode,
> admin1"
> Initializing...
> Done (postgis major version: 2).
> Output shape: Polygon
> Dumping: X [10 rows].
>
>
> Due to the resolution I’m looking at about a 130MB output file, so I won’t
> email it in here, but the batch processing is being done on an AWS EC2 server
> running Ubuntu 12.04LTS (This is the third server I have tried running it on)
>
> PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
> 4.6.3-1ubuntu5) 4.6.3, 64-bit
> POSTGIS="2.1.0 r11822" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23
> September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
> LIBJSON="UNKNOWN" RASTER
>
>
> Has anyone seen anything similar, any ideas where I can start.
>
> cheers
>
> Ben
>
>
>
>
> --
>
> Ben Madin
>
> t : +61 8 6102 5535
> m : +61 448 887 220
> e : b...@ausvet.com.au
>
> AusVet Animal Health Services
> Western Australia
>
> AusVet's website: http://www.ausvet.com.au
>
> This transmission is for the intended addressee only and is confidential
> information. If you have received this transmission in error, please delete
> it and notify the sender. The contents of this email are the opinion of the
> writer only and are not endorsed by AusVet Animal Health Services unless
> expressly stated otherwise. Although AusVet uses virus scanning software we
> do not accept liability for viruses or similar in any attachments. Thanks for
> reading.
>
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au
AusVet Animal Health Services
Western Australia
AusVet's website: http://www.ausvet.com.au
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete it
and notify the sender. The contents of this email are the opinion of the writer
only and are not endorsed by AusVet Animal Health Services unless expressly
stated otherwise. Although AusVet uses virus scanning software we do not accept
liability for viruses or similar in any attachments. Thanks for reading.
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] pgsql2shp output truncated
G’day all, I’ve been using pgsql2shp to create shape files for a client by creating a table which contains global level 1 administrative boundaries (3670 records). When I connect to the db, I can see this many records and display the appropriate geometries. select ccode, admin1, mapcode, st_mem_size(geom), st_isvalidreason(geom) from polbnda2013 where ccode like 'ZWE' ORDER BY 1,2; ccode | admin1| mapcode | st_mem_size | st_isvalidreason ---+-+-+-+-- ZWE | BULAWAYO|3230 |2496 | Valid Geometry ZWE | HARARE |3231 |3264 | Valid Geometry ZWE | MANICALAND |3232 | 108992 | Valid Geometry ZWE | MASHONALAND CENTRAL |3233 | 63616 | Valid Geometry ZWE | MASHONALAND EAST|3234 | 34080 | Valid Geometry ZWE | MASHONALAND WEST|3235 | 24432 | Valid Geometry ZWE | MASVINGO|3236 | 29840 | Valid Geometry ZWE | MATABELELAND NORTH |3237 | 28832 | Valid Geometry ZWE | MATABELELAND SOUTH |3238 | 69792 | Valid Geometry ZWE | MIDLANDS|3239 | 31296 | Valid Geometry (10 rows) When I run pgsql2shp -b -p 5432 -f outputs/2013/polbnda2013 -g geom -u ben -P noway oiemaps outputs.polbnda2013 Initializing... Done (postgis major version: 2). Output shape: Polygon Dumping: X [3665 rows]. The five rows missing are the last five rows in the table (Zimbabwe…). Ahh you say - an error in the data for Zimbabwe! But, if I just export Zimbabwe : pgsql2shp -b -r -p 5432 -f outputs/2013/polbnda2013 -g geom -u ben -P caitlin oiemaps "SELECT * FROM polbnda2013 WHERE ccode LIKE 'ZWE' ORDER BY ccode, admin1" Initializing... Done (postgis major version: 2). Output shape: Polygon Dumping: X [10 rows]. Due to the resolution I’m looking at about a 130MB output file, so I won’t email it in here, but the batch processing is being done on an AWS EC2 server running Ubuntu 12.04LTS (This is the third server I have tried running it on) PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit POSTGIS="2.1.0 r11822" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER Has anyone seen anything similar, any ideas where I can start. cheers Ben -- Ben Madin t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services Western Australia AusVet's website: http://www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by AusVet Animal Health Services unless expressly stated otherwise. Although AusVet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
