Hot answers tagged

5

http://postgis.net/docs/ST_Equals.html select ST_Equals('0101000020E6100000C5BCD8C5FEF45DC0AFE29EF87B584740'::geometry ,'0101000020E61000005A7CAFC6FEF45DC01A23C8F77B584740'::geometry) Returns False.


5

In 2.8 - 2.14 (Not sure about earlier versions) you can export shape files to PostGIS using the build in DB Manager. Open DB Manager In the list of PostGIS databases find the one you want to export to, find the desired schema and pres the button marked with the red outline. Choose the shape file in 'input' (Has to be in your 'Layers Panel') and press ...


4

Firstly, you're using ST_AsGeoJSON and giving it a GeoJSON feature as input. ST_AsGeoJSON produces GeoJSON geometry from a binary geometry. What you probably want is ST_GeomFromGeoJSON, which takes a string representing GeoJSON geometry, and produces a binary geometry. Note that ST_GeomFromGeoJSON only accepts the geometry part of the GeoJSON feature: the ...


4

You can open the properties dialog (for the original writer) and use the Writer option there to move that table from one writer to the other. That way you don't have to copy from one table to another. As the above screenshot shows, it doesn't even need to be the same format. FME will automatically adjust the data types to match the new format. However, ...


4

The shapefile specification states that the dBase file is limited to two gigabytes (2^31-2 [-2 not -1, because the records are counted in short integer chunks)]). Some open source utilities can handle one overflow (2-4Gb). 14Gb is far too large. Your choices: Break the original data source into ten 1.4Gb shapefile chunks (or seven 2.0Gb chunks) ...


4

You are likely correct in your thought that the conversion using ST_AsText is causing a loss of precision. This is actually stated as a warning in the documentation found here: ST_AsText WKT format does not maintain precision so to prevent floating truncation, use ST_AsBinary or ST_AsEWKB format for transport. Basically you want to do any operations ...


3

Instead of playing with workarounds try if GDAL can do the job for you directly. If command ogrinfo -al -so your_big_shapefile.shp seems successful you have good chance to have luck with ogr2ogr as well. Read http://www.gdal.org/ogr2ogr.html and http://www.gdal.org/drv_pg.html and try ogr2ogr -f PG PG:"dbname='databasename' host='addr' port='5432' ...


3

You must first UNION all the geometries. Then you can LEFT JOIN the geometries ON ST_Equals to retrieve the values. Sample code -- Create dummy test values WITH sand1 AS ( SELECT val, ST_MakePoint(x, y) geom FROM (VALUES (11, 0, 0), (22, 1, 2), (32, 3, 1)) a(val, x, y) ), sand2 AS ( SELECT val, ST_MakePoint(x, y) geom FROM (VALUES (12, 0, 0), (23, 1, ...


3

The "SPIT" plugin is no longer bundled with QGIS, as the plugin was unmaintained and has been surpassed by DB Manager and the processing database import algorithms. changelog.qgis.org/


3

You can execute SQL queries and load results as layers in QGIS using the DB Manager. If you still want to go through csv you must take care that WKT-geometry is included in the csv. You can make the query and save the result to CSV with ogr2ogr using the following command: ogr2ogr -f CSV -lco GEOMETRY=AS_WKT -sql "select avi, st_union(geom) geom from ...


3

PostGIS defines the = operator to test bounding-box equality rather than geometry equality (see docs). There have been discussions of changing this behavior, but I'm not aware of anything in the works. (Refer to https://lists.osgeo.org/pipermail/postgis-devel/2016-April/025769.html for a recent discussion.)


3

You can skip the CSV step and just go straight to shapefile using pgsql2shp, which is installed a part of PostGIS. You could also use ogr2ogr.


2

The package PostGIStools can help. See for example the vignette. Another way could be to transform your Spatial*DataFrame geometry to WKT, insert into PostGIS using the classic RPostgreSQL package and re-create the geom there.


2

You need PostGIS 2.3 which has not yet been released. Check SELECT postgis_full_version(); If it says less than 2.3, you are out of luck.


2

Another way to approach this, that may give you some flexibility for the longer term, is to create a view based on your query in PostgreSQL, then load that into QGIS with the standard PostGIS data loader. Basic SQL Query in Postgres: Create View from SQL Query, with unique ID Load View in QGIS Unioned layer displayed in QGIS The benefit to saving the ...


2

You can create views on your table to access the different geometry types as separate layers in QGIS, for example: CREATE VIEW parcel_polygons AS SELECT id, name, geom::geometry(MultiPolygon, 4326) FROM parcels WHERE GeometryType(geom) = 'MULTIPOLYGON'; CREATE VIEW parcel_points AS SELECT id, name, geom::geometry(Point, 4326) FROM parcels WHERE ...


2

Figured this out. This process should work for any shapefile held in a Postgis database, and will put it in a format that can then be plotted as a polygon in Tableau directly from the database; no mucking about importing or exporting stuff. It will create a new table containing the data in the shapefile in a format thats usable by Tableau. The more ...


2

You have most probably inverted the Lat and Lng values in either Leaflet or PostGIS. Let's have: Point1: Lat: -12.99835864475412 / Lng: -38.506194949150085 Point2: Lat: -12.999215865191118 / Lng: -38.50590527057648 Point3: Lat: -38.506194949150085 / Lng: -12.99835864475412 (inverted from Point1) Point4: Lat: -38.50590527057648 / Lng: -12.999215865191118 ...


2

The reason the Haversine equation is used for the 4326 calculation is that this is a geographic coordinate system. That means that the coordinates are still on a sphere, they are not mathematically transformed to a flat surface. A projected coordinate system would have this equation or some similar construct already "built-in", so to speak. The Web Mercator ...


2

PostgreSQL / PostGIS (pgAdmin3) create a view and use ST_GeomFromText (text WKT): text WKT = geo;


2

There is a tool bundled with PostGIS 2.2: PostGIS 2.0 Shapefile and DBF Loader Exporter It allows you to import and export.


1

You can backup via the write ahead logs (WAL-segments in pgxlog-folder) together with a normal copy of the data folder as well. It has the advantage of being faster than restoring from a dump (depending on the last snapshot), but that does only brings an advantage for larger datasets. Generally it is best to not store your data in the public schema as ...



Only top voted, non community-wiki answers of a minimum length are eligible