New answers tagged

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"


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


0

The solution seems to be using the "Value Relation" widget as the edit widget in the fields tab of the layer properties. I set the layer to my lookup table, the key value to the column containing the encoded integer column, and the value column to the string value column. Then I had to create a filter expression to match all the columns I require: ...


0

The easiest way (in my opinion) is to use GeoTools, if you follow the quickstart tutorial you will see how to draw the features of a shapefile on the screen using swing. Then all you need to do is change the input datastore to use PostGIS (a simple change to the parameters) and you are done.


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


2

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


0

For the record: Enterprise geodatabase libraries were available for both 32-bit and 64-bit servers at 10.0, and have shipped exclusively 64-bit since ArcGIS 10.1. There has never been a "32-bit bottleneck" in ArcSDE client applications (as of the time of the original post, the increased 64-bit instruction size actually slowed performance, though modern ...


0

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.


0

If you are on linux you can probably use unix socket. Then you need to have specified thu user name in the pg_hba file for local, with trust or pair as authentication. Then when you connect you shall not give a host name at all. At least in other softwares that gives you a local connection over unix socket.


0

If you're using ogr2ogr to load data, you can simply apply the append tag. -append Other than that it will depend on the solution you are using. The more common solutions for loading MasterMap data are: Go Loader FME


0

The issue (after removing all the ":" from column names) is the column named "natural", which apparently causes problems because it is a reserved word. I changed the column name to "nat" and now can view the data in ArcGIS without errors. The ArcGIS OSM extension recommended above seems to have a way around this (see ...


1

I made a test with OSGeo4W version of GDAL gdalinfo --version GDAL 2.0.2, released 2016/01/26 I created a test point in OpenJUMP JML format with one decimal attribute. <?xml version='1.0' encoding='UTF-8'?> <JCSDataFile xmlns:gml="http://www.opengis.net/gml" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance" > ...


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


2

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


1

Cause for this must have been an unstable internet connection (hotspot via Smartphone). One should not try to install various things from the internet (eg. OSGeo4w etc.) at the same time over a weak internet connection. Patience is required here! Second issue went away with reboot and reconnect to the PostgreSQL Database Server. No big deal at all.


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


0

You have not written what you want to do with the stops or why you want to know which stop belongs to which route. This might influence what the best solution to your problem is. The data you need is in the DB. If we look at Relation 1658527 as example you get the routes out of planet_osm_line by looking for osm_id -1658527. The information about the stops ...


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.


0

I ended up doing: UPDATE mytable SET wkb_geometry=ST_makevalid(wkb_geometry) WHERE NOT ST_isValid(wkb_geometry));


0

If you return a query with 'ST_AsText(geom) as geomwkt' and fetch the result into data, you can use: library(rgeos);library(sp) wkt_to_sp <- function(data) { #data is data.frame from postgis with geomwkt as only geom SpP <- SpatialPolygons(lapply(1:length(data$geomwkt), function(x) ...


1

Your first statement is correct in what you're trying to achieve - at least testing on my PostgreSQL 9.3 server. Let me explain: The trigger fires BEFORE the changes are applied to the table. In this state, it already has information about the OLD content of the row and the NEW content of the row (i.e. how the row will look like after the changes implied ...


1

ST_Difference Returns a geometry that represents that part of geometry A that does not intersect with geometry B. postgis.net/docs/ST_Difference.html


0

To create a new user, this works for me: sudo -u createuser -s <desired_user_name> Then you can psql <desired_user_name>


0

Assuming you are working in linux command line, try: sudo su postgres Or if you think your admin username is 'psql', just replace 'postgres' with 'psql'.


1

First, use only the select and st_astext() to check the results: (adapt to your fields) select test_data_,id_field,test_data_.geom,st_astext(ST_OffsetCurve( test_data_.geom, test_data_.buffer_dis, 'join=mitre mitre_limit=5.0')) If all right then: select test_data_,id_field,test_data_.geom,ST_OffsetCurve( test_data_.geom, test_data_.buffer_dis, ...


1

You can change the getFeatureInfo template used by geoserver, see section "Advanced Formatting". You could try to intercept the ${attribute.rawValue} and print this directly or modify it before sending the feature info back.


0

I was also following the OP's installation method of PostgreSQL and PostGIS, namely sudo apt-get install postgresql-9.4-postgis-2.1 pgadmin3 postgresql-contrib-9.4 I enabled the PostGIS extension in a database but didn't get shp2pgsql to work until I did sudo apt-get install postgis as listed toward the bottom of the page. Why postgresql-9.4-postgis-2.1 ...


1

You are missing a union call and also 0.1 is pretty low for a concave hull, try something like 0.7. (that is really the shrink parameter so 0.1 would be like target to 10% of convexhull area. The smaller the value the longer the processing and more likely you'll run into invalid geometry) SELECT SUM(ST_AREA(ST_CONCAVEHULL(ST_Union(wkb_geometry), ...


1

probably you are not entering correct password. Please make sure you can access db from pgAdmin. Then use same user/password (copy&paste).


0

Try this Oracle environment variable: NLS_LANG=AMERICAN_AMERICA.UTF8


-3

It works to me when i avoid the mismatch SRID's. SRID's must be identical


4

QGIS + PostGIS should handle a 400 MB dataset without too much grief, give it a try. One of the most important aspects is to ensure you have a spatial index of the geometry column, e.g. CREATE INDEX roads_geom_idx ON roads USING gist (geom); which will allow it to load quicker in QGIS when you zoom into a specific area. And if you are classifying / ...


0

I have uncommented the following line which redirects the sql to stdout; #postp.pipeOut = true Now the issue resolved after I commented the line or can make the parameter to false.


0

Haven't tested this with an SQL database but you could use the following code in the Python Console to export loaded layers as shapefiles: results_path = "C:/Users/You/Desktop/Folder_Name//" layers = QgsMapLayerRegistry.instance().mapLayers().values() for layer in layers: QgsVectorFileWriter.writeAsVectorFormat(layer, results_path + ...


2

The current implementations of ST_ClusterWithin and its friends ST_ClusterIntersecting and ST_ClusterDBSCAN (PostGIS 2.3) are all Cartesian-based, so they won't work on geographic coordinates. Your best option is to do as @JohnBarça says and transform your coordinates to a planar system in meters. The "private" function _ST_BestSRID might be of some use ...


1

There is an extension called "Batch save vector Layers". With this tool, you can save multiple files as ESRI Shapefiles in a user defined destination folder. Note that the extension is marked as experimental, but everything works fine for me so far.


0

This is not an error, this is a warning that you are trying to get the value for a point lying outside the raster area. It's not because a geometry ST_Intersects() with a raster that the ST_StartPoint() or the ST_EndPoint() of this geometry actually intersects with this raster, hence the warning. If you want no failure for all requested point you must test ...


0

I sounds like you posibbly have a mix of 32 bit and a 64 bit versions of PostGres and PostGIS that are not mutually compatible with each other. I find that the simplest way of installing Postgres with Postgis on Windows is to use the Enterprise DB installer. Using this install tool it is a very simple matter to get it all up and running. You can also ...


1

@> is not a PostGIS bounding box operator. It is a PostgreSQL geometric operator with the box type. So essentially, the query in your question casts the PostGIS bounding box to PostgreSQL box types. Box types are not equivalent to point-in-polygon operators required for your problem. Furthermore, mixing PostGIS types and PostgreSQL geometric types is ...


3

ST_MakeBox2D already returns a geometry that is a polygon for PostGIS. The right syntax has points in lower left-upper right order but function does not seem to be strict with that. SELECT ST_MakeBox2D( ST_GeomFromText('POINT (0 0)'), ST_GeomFromText('POINT (1 1)')); "BOX(0 0,1 1)" SELECT ST_MakeBox2D( ST_GeomFromText('POINT (1 1)'), ...


1

Make a set of unique geometries by deleting duplicates (following the example you have already found - create as a new table). In the new table, create an attribute to uniquely identify each polygon (you could do this as part of step one by adding a serial field). Use the new table to do a spatial join on the original table. this will add the new unique ...


0

As far as I know SFCGAL cannot be added to an existing PostGIS installation, if the installation was compiled without configuring for SFCGAL support. Here is some more information on that issue. You will have to compile a SFCGAL enabled version, how to do that for the software versions listed below is described here. Unbuntu Trusty Tar 14.04 LTS GEOS 3.5.0 ...


0

I usually try to convert any files to Shapefiles via ogr2ogr and then load it to PostgreSQL, usingogr2ogrorshp2pgsql`. A little bit complex, but i save files for history and it's easy to see changes in attributes. ogr2ogr -f "ESRI Shapefile" your_shape.shp your_qpx.qpx -sql "SELECT somefieldfromgpx AS somefieldfromdatabase FROM your_qpx" note that ...


1

It seems the PostGIS version you are using does not provide SFCGAL support. I managed to compile PostGIS 2.2.2 with SFCGAL 1.3.0 support for PostgreSQL 9.5 using following script, which is based on this. You need to get the latest CGAL library first using the PPA given on this webpage. I was facing some 'missing keys errors' on the way I was able to resove ...


0

I found a solution: SELECT tx_county.name AS "County Name", tx_county.geoid AS "GEOID", puma.pumace10 AS "PUMA", (tx_county.aland <= puma.aland10) AS "Within", (tx_county.density >= 5,000) AS "Urban" FROM tl_2015_48_puma10 as puma, (SELECT * FROM tl_2015_us_county WHERE statefp = '48') AS ...


1

I believe you could solve this with a case statement: CASE WHEN st_within(st_centroid(tx_county.geom), puma.geom) AND tx_county.aland <= puma.aland10 THEN 'TRUE' ELSE 'FALSE' END AS "Within" You would need to revise how the tables join, likely using ST_Intersects(puma.geom, tx_county.geom) that way you don't end up with ...


2

The invalid GeoJSON object error appears to be a product of your edited code, where the temp variable is actually a string, not a GeoJSON object. From your sample output in the comments, it appears that you are getting an array of GeoJson Polygon objects, which L.geoJson can interpret correctly, so your original code (before the edit) should successfully add ...


2

Assuming that the coordinate system of your points uses meters, you can produce boxes centered around those points with ST_Expand. For example: SELECT ST_AsText(ST_Expand('POINT (0 0)'::geometry, 4)); st_astext -------------------------------------- POLYGON((-4 -4,-4 4,4 4,4 -4,-4 -4))



Top 50 recent answers are included