New answers tagged

2

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, ...


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.


0

I could not make the UPDATE work so I created a new table CREATE TABLE madclip AS SELECT mad.gid,mad.cat, CASE WHEN ST_CoveredBY(mad.geom,clip.geom) THEN (ST_DUMP(geom)).geom::geometry (Polygon, 904483) ELSE (ST_DUMP(ST_Intersection(mad.geom,clip.geom))).geom::geometry (Polygon, 904483) END AS geom FROM mad AS mad INNER JOIN clip as ...


0

I think you're on the right track, you just need to add an additional join: select sand1.value, sand2.value, sand3.value, sand1.geom from sand1 inner join sand2 on st_equals(sand1.geom, sand2.geom) inner join sand3 on st_equals(sand1.geom, sand3.geom);


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.


0

First, you should realize that this is more of a general Postgres question, since all Postgres databases can be backup up, whether or not they are PostGIS. If you find you need more detailed answers, you should probably ask this question on dba.SE. The standard way to back up a Postgres database is the pg_dump utility. You're question is extremely general, ...


0

Avoid loading the shapefile in QGIS first - use the import layer/file button in the DB Manager dialog. Select the file, not a loaded layer, with the button to the right of the Input box. You'll need to specify CRS and have the option to reproject in the same step: Tested this with 200,000 features - ~ 1 minute to import.


2

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

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 file into 10 1.4Gb chunks Reduce the file width to 1900-2000 bytes (this assumes the ...


0

Step -1 Set SRID to your Table - SELECT UpdateGeometrySRID('Table Name' , 'the_geom' ,32645) Step -2 SELECT *,ST_X(ST_Transform (the_geom, 4326)) AS "Longitude" ,ST_Y(ST_Transform (the_geom, 4326)) AS "Latitude" from "Table Name"


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 ...


0

I once wrote a UTFgrid function for PostGIS to be used with OSM data. It's not particularly what you're looking for, but it might give you a start. I don't know how it would work with raster though. https://github.com/nationalparkservice/places-api/blob/7f048c2addebd9772e4718a4f0df4e44e981d0a3/scripts/sql_scripts/pgs/func_o2p.sql#L338-407


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 ...


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 ...


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.)


5

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


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 ...


0

Or perhaps it is because you are transforming your ways to srid=4326 (WGS 84) to compare against your linestring with an srid=900913 (Deprecated Mercator)? ST_Transform( marks.way, 4326 ) st_setsrid( ST_GeomFromText('LINESTRING(121.1527761 13.9413653,121.154058 13.9413113,121.1550161 13.9412372,121.1559079 ...


0

Try analyzing your sub-statements to find out why it isn't working. I had a compound select statement such as yours which worked on one set of data but didn't on another due to the statement referring to the record id rather than a target or source id. Doing so, you may discover that you are referencing/comparing the wrong geometry srid. From your ...


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

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


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, ...


0

Haversine distance formula calculates on a sphere, which is going to be a closer calculation than a straight line distance calculation that does not take into consideration the curvature of the Earth. This Math SE Q&A may give you some additional insight. From link: The Haversine formula gives the "as-the-crow-flies" distance, i.e., the great ...


2

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


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

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/


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 ...


0

Right click on your new writer then Choose Copy from Feature type context menu and then choose your attribute writer..I hope this will help you...


0

I recently setup a web mapping application with OL 3.15, calling a WFS layer published by mapserver 7.0, which uses PostGIS based data tables backend. Here are the relevant parts of the mapfile: CONNECTIONTYPE postgis CONNECTION "dbname=water host=127.0.0.1 port=5432 user=water_dba password=<your-db-password> " DATA "geom FROM ...


0

UPDATE Schema.Table1 SET Geom1 = ST_AsEWKT(ST_LineInterpolatePoint(ST_LineMerge(ST_Force2D(a.geom)), Schema.Table2.Milepost/(St_length(a.geom)/1600))) FROM Schema.Table1 As a where a.RoadName = Schema.Table2.RoadName This Query did the trick to Geocode and update the values.Thank You Guys....


0

First. I am not very familiar with MapServer but according to it's documentation, In order to enable MapServer to serve WFS, it MUST be compiled against certain libraries Second, you have to configure a PostGIS connection in your MapServer, documentation for that here. Third, you will have to create a mapfile to describe the layer to MapServer. ...


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 ...


0

Have you considered GeoNode? It's based on Django which is used for user/groups authentication and it is using the same stack you mentioned (Postgres/PostGIS Geoserver & Openlayers) http://geonode.org/


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 ...


0

Check this review of 8 MOBILE GEODATA COLLECTORS FOR ANDROID http://www.50northspatial.org/8-mobile-geodata-collectors-android/ Below I will review a number of geodata collectors for Android platform. I only pick out the solutions I have dealt with myself, hence all written below is my own experience, no more. Furthermore, I will only consider free or ...


3

ST_Scale takes scale factors for each dimension, and not a percentage for the area. This means you cannot simply expect to use 0.01 and expect to see 0.01% of the area. Area has dimensional units [length] x [length], so a scale applied to each of these dimensions will raised to the power of 2. Therefore, you need to apply a sqrt(scale). For example, here ...


0

WMS do support the filter (EQUALS, DISJOINT, INTERSECTS, TOUCHES, CROSSES, WITHIN, CONTAINS, OVERLAPS, RELATE, DWITHIN ) and openlayer-3 or 2 can reload the wms images according to filter refer http://docs.geoserver.org/stable/en/user/tutorials/cql/cql_tutorial.html


0

Given your 0% match combined with your mention of digitized historic maps (big props to you for putting in the work required to represent change over time!!) it's likely that you need to link some additional attribute data to your roads before any sort of geocoding will work. every road segment, for example, in the u.s. census bureau's road network is ...


2

In PostGIS 2.2, your options are to either (a) do a join after-the-fact to get back your IDs or other relevant information, or (b) abuse the Z or M ordinates to sneak some additional information into the geometry objects. The newer clustering options that will be released in PostGIS 2.3 are more flexible; they're implemented as windowing functions and can ...


6

Create a Feature layer and then use Select Layer by location with a polygon of your extent. Example: import arcpy arcpy.env.workspace = r'D:\Arenden_D\Dribbel\Dalsland geodatabas\Dalsland.gdb' fc = r'Dalsland' fcselectionpolygon = r'selection' arcpy.MakeFeatureLayer_management(in_features=fc, out_layer='fclyr') ...


2

First thing you need to do is to set your date field correctly as otherwise you canĀ“t order it. Something like this(refer to the documentation for the correct time format): alter column time TYPE timestamp USING time::timestamp without time zone Or if you want to have directly in your query without changing the column you can use the CAST function. Then ...


2

PostgreSQL partitioning is only going to make things faster if you have a non-spatial constraint in your query and that constraint is applied to each child table in the partition via constraint exclusion. (The constraint exclusion config also has to be turned on.) Partitioning will be useful for performance, if you have a key you can build constrained ...


5

By using CTEs you are forcing the planner to evaluate each CTE component without looking at any other part of the query. Once you've extracted the results from the tables using the CTEs the fact that there is an index is lost to the planner. You've created a new, non-indexed relation in the CTE. In general, if you can write logic as a plain join, always do ...


1

Not sure what do you mean with "a house next to it" but here an example of what you can try: SELECT a.id, a.geometry, 'T'::text as type FROM houses a, houses b WHERE ST_Intersects(a.geometry,b.geometry) AND a.id != b.id Could be done with other spatial operator (ST_DWithin could be a better candidate). Better with Gist index on geometry field.


0

SELECT ST_AsEWKT( ST_SetSRID( St_GeometryFromText(' MULTIPOLYGON(((295098.566494 6006717.0377012,344018.26458971 6009163.022606,329342.355161 5972473.2490342,295098.566494 6006717.0377012))) ') ,900913) ) SELECT ST_AsText( ST_SetSRID( St_GeometryFromText(' MULTIPOLYGON(((295098.566494 6006717.0377012,344018.26458971 6009163.022606,329342.355161 ...


0

This line causes the output to be directed to the screen postp.pipeOut = true please see this link for more information.


0

Check your osm2po config file to ensure you create the desired routing table. See this post


1

Cadcorp SIS 8.0 is most recently tested with PostgreSQL 9.3.4 and PostGIS 2.13. These are therefore recommended, but it will run with any PostgreSQL 9 and PostGIS 2 combination. If you need to use an earlier version, then PostgreSQL 8.4 and PostGIS 1.4 with corresponding PostGIS WKT Raster are also supported.


2

I think it's a question of what bit-type your ODBC driver has been created as. MapInfo Pro 15.2 is 64-bit as you write yourself. EasyLoader is however still a 32-bit application so it does require a 32-bit ODBC driver to work. There is a 64-bit version of EasyLoader on the way. It is due before summer 2016.



Top 50 recent answers are included