New answers tagged

2

I used Buffer to limit out the points and then calculated bearing between buffer points(coordinates) and saved that in MySQL table. Nearest Neighbor and bearing gave me next point of road.


2

The error you're getting is because you're trying to restore a database created with an old version of PostGIS (2.1) to a newer version (2.2). I would recommend trying to downgrade your PostGis version to 2.1.x by using the following command in your database, assuming you have no data loaded in it (Please backup your current DB if you do!) DROP EXTENSION ...


0

Adding to Alexandre's very useful answer, if some of your census units may span three of your towns (and therefore you can't guarantee more than 50% falls in any town) you can do this: select distinct on (b.id) b.*,t.name, (st_area(st_intersection(b.wkb_geometry, t.wkb_geometry))/st_area(b.wkb_geometry)) as proportion from blockgroups b, towns t where ...


0

I have done it this way: UPDATE data_tiled t SET raw_data = f.raw_data_tile FROM ( SELECT data_time, data_type, ST_Tile(raw_data, 250, 250) As raw_data_tile FROM data_full WHERE data_time = '2015-12-06 00:00:00' AND data_type = 'a' ) as f WHERE t.data_time = f.data_time AND t.data_type = f.data_type AND ST_UpperLeftX(t.raw_data) = ...


2

You can confirm that the discrepancy is a bug by comparing the results of _ST_DistanceUnCached(geog,geog) to _ST_DistanceTree(geog,geog) What is happening under the covers is that the "tree" algorithm only kicks in when a geometry shows up in a query a couple times in a row. At that point the geometry is cached and a tree built on it, for faster distance ...


0

I installed postgis-2.2 under Ubuntu 16.04 alpha2 using apt-get on default repositories. And then CREATE EXTENSION postgis_sfcgal; works out of the box. test=# create extension postgis_sfcgal; CREATE EXTENSION test=# select postgis_sfcgal_version(); postgis_sfcgal_version ------------------------ 1.2.2 (1 row)


2

Your original trigger contains an error: CREATE TRIGGER area_calculate **BEFORE** INSERT OR UPDATE ON nutzungen This means that you're triggers calls the function on geom before the geometry is updated. Replace the trigger by calling the following: DROP TRIGGER area_calculate; CREATE TRIGGER area_calculate AFTER INSERT OR UPDATE ON nutzungen FOR EACH ...


0

If you are installing from binaries (rpm), then you should just let the package manager install the recommended version. If you are building from source, or are just curious otherwise, the absolute minimum PostgreSQL version for QGIS 2.8 appears to be PostgreSQL 8.0, as documented in the build instructions: Optional dependencies: for postgis ...


0

Please check the following link. this might help you. Good Luck PostgreSQL default values in QGIS 2.8.1 "QGIS 2.8.1, OsGeo4W built against PostgreSQL Client Version 8.3.10,"


2

I'd check the following: first of all, I'd like to be sure that source data are OK, by comparing them with official/cadastral data I'd try to use the qgis openlayers plugin to check if the transformation to 3857/900913 works properly for both 23031 and 25831 Try to reproject using QGIS, just saving the shapefile with the target srid. If using QGIS works ...


2

You must convert point coordinates to geometry SELECT ST_Within(St_GeomFromText('POINT(-73.883573184 40.751662187)'), ST_GeomFromGeoJSON(d));


1

Complementing, previous answer.. montevideo-release-v4 is the latest branch. There is a lot of clean up that needs to be done as everything we coded is still there even if its not used.


0

This is a well known issue in osm2pgsql. See https://github.com/openstreetmap/osm2pgsql/issues/16 . This issue is raised when osm2pgsql slim mode is used and multiple geometries are imported. Don't use osm2pgsql slim mode, then the issue will probably go away. edit: Or it might be a stale version of osm2pgsql: ...


0

This page suggests a panel of ways to extract OSM data and display them into Desktop GIS. These are not necessarily based on command lines. It might help you.


0

I've come up with a solution which works: SELECT x.bufid, (ST_SummaryStatsAgg(x.intersectx,1,true)).* FROM (SELECT bufid, ST_Intersection(rast,1,ST_AsRaster(geom, rast),1) as intersectx FROM rasttable, buftable WHERE ST_Intersects(geom, rast)) as x WHERE x.intersectx IS NOT NULL GROUP BY bufid I think the issue was with the ...


4

QGIS has its own internal handling of layers and features. Different data "providers" are used to pull layers in from a variety of sources (eg PostGIS, OGR file types, WFS servers, etc) and translate them into QGIS' own internal formats. Once you have a project fully loaded, including a .shp file plus a .dbf, plus another CSV joined, are all the data in ...


3

1500x750 is pretty big for a raster. Definitely worth retiling your data. Take a look at ST_Retile function (new in PostGIS 2.2) - http://postgis.net/docs/manual-2.2/RT_Retile.html or if you are using PostGIS 2.1, you can use ST_Tile instead which achieves same purpose on a per row (instead of per table basis) ...


5

The column format for PostGIS is called geometry and you'll probably need to read up on all the functions associated with managing spatial columns. If you are getting the lat/longs from GPS or Google, the projection will be EPSG:4326. edit: iant's comment brings up a good point about the geography type, and indeed if you are using EPSG:4326 it looks like ...


0

If your LA boundary data consists of 1 single polygon, it's easy: SELECT a.* FROM tmp_coords AS a INNER JOIN shp_laboundary AS b ON (St_Intersects(a.st_loc, b.geom)) You can safely use intersects, it's faster than within. If you have multiple polygons you first have to merge them with ST_Collect


1

You can use the build in import plugin that is installed when you install the PostGis extension in PostGres You can find it under Plugins in the menu and is called the PostGIS Shapefile and DBF loader watch this youtube video as an example


3

Osmium is a very fast and flexible C++ library for working with OSM data: http://osmcode.org/libosmium/ It supports all types of osm entities such as nodes, ways, relations and even historical osm data. It's published under Boost Software License and therefore free to use.


0

Looking into PostgreSQL Data Types Documentation it seems that there is no FLOAT data type. Try using numeric instead. ALTER TABLE planet_osm_line ADD vertices numeric; INSERT INTO planet_osm_line(vertices) SELECT st_NPoints(way) FROM planet_osm_line;


1

ALTER TABLE planet_osm_line ADD COLUMN vertices INTEGER; Why float? Are there fractional vertices? You are also inserting records, when I think you actually want to update each way with the number of vertices within. Use an update function to add the values to the new column based on the result of the function operating on the spatial column. For example, ...


1

Solution: 1) Use psql in the command line instead of loading with the SQL editor. 2) If you're using Windows and not logged in as postgres, add the -U flag to: psql -d postgis_in_action -U postgres -f ..path/ch11_data.sql -- for some reason, the username is not being picked up from the pgpass.conf file.


2

They're two different representations of the same binary data. SET bytea_output = 'hex' will switch to the hex representation that it sounds like you want. Here's a couple of examples: postgres=> SET bytea_output='escape'; postgres=> SELECT ST_AsBinary('POINT (24.3 41.7)'::geometry); st_asbinary ...


0

To remove a table from Table Registry you need to execute the sde command: sdetable -o unregister -t av.world -u av -p mo -i 7654 This command won't remove the objectid field, so you need to do manually.


0

I got the same error so I show the errorlog from postgres and got this: ERROR: function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) is not unique at character 8 HINT: Could not choose a best candidate function. You might need to add explicit type casts. STATEMENT: SELECT ...


2

The problem is that SQL view in geoserver doesn't allow to use semicolon(';') in SQL statement. Geoserver only allows one query statement. You didn't provide the structure of Your database, but may be it's possible to build SELECT query with subquery and get the same result.


2

Your logic seems sound, but you have some surplus text in your query which throws an error: "::geometry(linestring, SRID)" is not required. Also note that ST_ExteriorRing() does NOT work on multi-polygons, only polygons (null will be returned from multipolys, which may explain your lack of results). Also if you are using more than one polygon in each table, ...


1

It seems you can't download the original rainfall data, and all data available can only be used for comparing cells in a given image or detecting trends for specific regions between data sets. In "standard" data types (as they call them) such as JPEG and PNG, "Dark blue areas show where a lot of rain has fallen. Pale green to turquoise show increasing ...


4

This technique will work in versions of PostGIS < 2.2.0. Create cluster polygons by buffering the points by half your cluster distance, assign a cluster ID, then transfer the cluster ID to the points the polygons intersect: --Buffer all points by the half the cluster distance (in this case 40 metres), --merge into a multipolygon using st_union(), --then ...


5

If you can updgrade to postgis 2.2.0 you might be lucky because that feature has just been introduced. From the doc: "ST_ClusterWithin is an aggregate function that returns an array of GeometryCollections, where each GeometryCollection represents a set of geometries separated by no more than the specified distance." Check this: ...


3

Yes, this is common practice to speedup queries. As less data you load as faster will be the response. Bounding box selects are the fastest way to select a subset of the table. Don't forget to have an index on the geometry column though. The following answer may give you an idea how to write your query: Why is any pgr_* routing function taking forever based ...


1

This seems like something that could be done much easier than the answers submitted. I would use a simple python script personally: floodName = "the layer name here" boundryName = "the layer name here" fieldName = "the name of the field to contain the output 1/0" minCoverage = 0.5 # the minimum amount of area covered to write 1 updateMap = [] # this will ...


0

This may not fully meet your requirements, however I have just tested having Archiving enabled on both Parent and Child replicas using one-way replication. Edits made on Parent and then replicated through to Child show up in both the feature class and the archive in the child database. Child Replica Archive: Unfortunately if a feature is edited more than ...


0

In QGIS: Try Vector->Geometry Tools->Singleparts to multipart (QGIS 2.8.3). In Unique ID field select 'xxxxx' elevation attribute of input shapefile. Result - multilinestring shapefile with unique 'elevation' attribute.


1

You need to use two tools in the "Vector / Geometry" menu: "Dissolve" the layer using the elevation field as the dissolve field. How well this works depends on how well the line endpoints match up at the tile boundaries. If the endpoints don't match well enough for the lines to dissolve together, then you can use the PostGIS-based fuzzy-matching ...


4

You can render your data from PostgreSQL directly. Documentation here. PostgreSQL layer example: LAYER NAME "province" STATUS ON TYPE POLYGON CONNECTIONTYPE POSTGIS CONNECTION "host=127.0.0.1 port=5432 dbname=xyz user=postgres password=postgres" DATA "the_geom from xyz" CLASS STYLE COLOR 128 128 128 ANTIALIAS true END ...


1

It seems that SELECT seq from ways order by geom <-> ST_SetSrid(ST_MakePoint(lat, long), 4326) limit 1; did the trick


2

I have the same problem with char default values. Varchar, boolean and numeric are ok. Qgis version 2.8.5 drop table prova; create table prova ( gid serial primary key, test_bo varchar(100) default 'false' not null, test_bo2 varchar(100) default 'false' not null, geom geometry(Linestring,32632) ); Works drop table prova2; create ...



Top 50 recent answers are included