Hot answers tagged

7

Overlay operators like Contains, Intersection, SymDifference, etc. require exact noding. This is because there are floating point differences on the order of 1e-14 when interpolations are performed between two lines that are not equal. And while 1e-14 is a tiny number, it is still not zero. Use ST_Snap to help "snap" vertices from one geometry onto another ...


5

ST_Area will use the units in your coordinate system, which as you say are degrees. So you have two options: transform your geometries to a coordinate system that uses feet or meters cast your geometries as geographies. ST_Area will then perform the calculation in meters. You then need to convert from square feet or square meters to square miles. Your ...


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


5

If you have imported a planet or extract some time ago and have now downloaded a (much) newer planet or extract: It does not really make sense to do any updating as I think calculating and applying the diff will not save you time. Just re-run osm2pgsql again and it will remove the tables and create new ones resulting in updated data. If you want to keep ...


4

There is a configuration switch for this in GDAL 2.1 onwards: http://www.gdal.org/drv_geojson.html It defaults to 16 decimal places. So upgrading to GDAL 2.1 should solve your issue. If not you can try a higher number of decimal for floating point numbers: -lco SIGNIFICANT_FIGURES=17


4

At the scale of your display, these lines appear to be coincident, but in reality they're not: I've exaggerated the difference between the two lines using the "Magnify Topology" tool in JTS TestBuilder, which I highly recommend for looking into cases like this. Depending on the goal of your analysis, it might be suitable to snap your vertices to a grid ...


3

Assuming you have two tables "a" and "b". Table "a" is the table to be cut. CREATE TABLE a ( id serial PRIMARY KEY, geom geometry(MultiPolygon,31370) ); Table "a" is of type multipolygon because we don't know yet if some polygons will be separated in multiple parts afther the "cookie cut". CREATE TABLE b ( id serial PRIMARY KEY, geom ...


3

This can be done a bit more simply with json_build_object in PostgreSQL 9.4+, which lets you build up a JSON by supplying alternating key/value arguments. For example: SELECT json_build_object( 'type', 'Feature', 'id', gid, 'geometry', ST_AsGeoJSON(geom), 'properties', json_build_object( 'feat_type' : feat_type, ...


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


2

You need to define how the tables included are related. Also, for this purpose you have st_shortestline, that creates the line for you. The syntax is: Select st_shortestline(a.geom, b.geom) From a, b Then you probably want to put some where clause there or you will get all possible combinations between the tables.


2

Layers published as sql views are not editable, you have to publish them as normal tables instead


2

There's two pieces to the performance pie here: The time to calculate the tagging, which you should break out by doing some test runs with things like CREATE TABLE tract_pt_counts AS SELECT Count(*), tract_id FROM tracts t JOIN pts p ON ST_Intersects(t.geom, p.geom) GROUP BY tract_id The time to update a table in place. This is actually probably a lot ...


2

The reason for the error is most probably that the geometry type of the WFS feature type is defined to be MultiLinestring or MultiPolygon, but QGIS it trying to insert simple Linestring or Polygon. You can check the WFS geometry type by making a DescribeFeatureType request. For example ...


2

Using views (as suggested in link) is pretty easy, for example. -- population table (the table you edit when changes...) CREATE TABLE pop_table ( gid integer primary key, population integer, geom geometry); -- Density view (you can open it like a table in QGIS) CREATE VIEW density_table AS SELECT gid,population,(population/ST_Area(geom))::float AS ...


2

Replace ST_GeomFromText('POINT(715127 957625)',4326) with ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(715127 957625)'),SRID_YOU_ARE_USING),4326) 4326 is the SRID of WGS84, which is used by GPS, and is the commonly used lat-lng system you're looking for.


2

You can do your own function in PostgreSQL like this (Example taken from the docs) : CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, 'three'); INSERT INTO foo VALUES (4, 5, 'six'); CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS $BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM ...


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


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


1

You could use a subquery and ST_Union select ST_AsTexT(ST_Union(a.singlegeom, b.singlegeom )) from ( SELECT ST_COLLECT (ST_Simplify(geom ,. 005)) AS singlegeom FROM PUBLIC .mzones WHERE ID = ALL (ARRAY [ 'GMZ730' ]) ) a, ( SELECT ST_COLLECT (ST_Simplify(geom ,. 005)) AS singlegeom FROM counties WHERE fips IN ...


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.


1

This is WKB format wichs is a binary representation of the geometry. To select the geometry in GeoJSON format for example you can use : ST_AsGeoJSON()


1

This query can be improved in a number of ways. As was pointed out in comments, ST_DWithin(ad.geom, street1.geom, 30.0) OR ST_DWithin(ad.geom, street1.geom, 50.0) is just going to return everything within 50m, because everything with 30m is also within 50m. If there is something you were trying to accomplish other than joining all streets within 50m, you ...


1

Read your CSV file and create a vars_list list of tuples to insert. Reading the CSV file is off topic, but it should have a structure something like this: vars_list = [ (lng1, lat1, lng2, lat2), # first record (lng1, lat1, lng2, lat2), # second ... ] Then insert them all at once with executemany, like this: sql = '''\ INSERT INTO ...


1

The error message and specifications are pretty clear: GeoJSON supports the following geometry types: Point, LineString, Polygon, MultiPoint, MultiLineString, and MultiPolygon. PolyhedralSurface is not in that list, so you'll need to convert it to another geometry type.


1

The first step should be to enter your data in a PostGIS database. Then you have to connect MapServer to PostGIS and create layers for each of your tables or views in the PostGIS db. Finally you will have to load these layers in OpenLayers.


1

There are two views that you need to check geometry_columns and geography_columns that will provide you with a list like: "ian";"public";"coastline";"geom";2;27700;"MULTILINESTRING" "ian";"public";"motorway";"geom";2;27700;"MULTILINESTRING"


1

Updating is often quite slow. If it is possible, it is probably a lot faster to recreate the whole table like create table new_table as select pnt.*, poly.name from point_table pnt inner join poly_table poly on st_within(pnt.geom, poly.geom); I do this a lot and rename the new table to the original name and recreate indexes and constraints on it. If it ...


1

You can not connect to your database in Qgis if your dB is not spatial. Please run CREATE EXTENSION POSTGIS; on your db in pgadminIII and Retry to connect


1

It looks like you have confused the username and the database name. If you created a database called shashi, you should have Database: shashi in the QGIS PostGIS Connection window. Your username is likely postgres. If that doesn't work, look in the message log. My logs are in /usr/local/pgsql/var/, but they may be somewhere else on your machine.



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