New answers tagged

0

I just placed the first bit that i wrote to a seperate table on the place where i refer to the table. Where eventually subline.st_linesubstring was the tricky bit. -- SELECT SUBLINE WHERE INPUT POINTS ARE SNAPPED TO LINE -- SELECT SUBPOINTS WITHIN DISTANCE OF SUBLINE SELECT points.geom, points.id FROM tbl_all_points AS points, (SELECT ...


0

I understand (and correct me if I am wrong) that when you said: (...) those outside of APAC are still visible on the map. You are viewing those countries in the basemap. So, in order to get rid of the basemap, you have to click on the Basemap button on the left-bottom corner of the Editor. Then, select a background color (with good contrast with your ...


1

There are several alternatives using CARTO to solve the points-overlapping problem. First, if you have a timestamp column and the deaths have happened in two different dates, you can use Torque. That is an animated map. Second, the Wizard of the old Editor allow you to aggregate points in clusters, intensity or density maps. But my favorite example is using ...


3

You seem to have a PostGIS installation that was created by loading the postgis.sql file, rather than using the CREATE EXTENSION postgis command. So when you dumped your database, you got not only the data, but also all the function definitions, which includes references to the 2.1 PostGIS library. Install PostGIS 2.1.8 on your new system. Create a blank ...


0

It should be easy to set up: in a slightly different - but actually similar - raster context, I had to do update-retiling for a bunch of GeoTIFFs that cover an entire city, with different sets of geoTIFFs at different ranges of zoom from 9 to 18. The GeoTIFFs also didn't 'fit' neatly into TMS/Google Maps tile numbers. Rather than re-tile the whole job (...


4

I like to use the json_build_object function. SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM ( SELECT 'Feature' As type , ST_AsGeoJSON(lg.geometry)::json As geometry , json_build_object( 'attribute1', attribute1, 'attribute2', ...


0

I have found an alternative way to solve this question. Following this thread: https://geonet.esri.com/thread/16118 . By performing a Many to One Join where the points are the target features and the polygons are the join features. Then running a definition query against the new layer like this: dn in (select max(dn) from dn group by osm_id) This will ...


0

Because you said "Tiger_data schema does not have any tables"... After you install the extension there is still a lot of work to be done. 1. Update the loader platform for your setup This command will show you the defaults: SELECT * from tiger.loader_platform; I recommend you create a new record with a unique OS value so you can retain the defaults. ...


1

Your syntax is wrong. But in the documentation is exactly the example given you need. Just took an example for streetname from the first basic example in the documentation an put it together to an insert statement. Did not test it. It is also important how your table is structured and that you have the appropriate columns as it is needed for the correct ...


0

If you don't find another solution for this, consider creating a VIEW in PostgreSQL and then add that as a layer. This has the added benefit that you can change the query in the database without touching your Python code. (So, for example, if you have several users using QGIS with this code installed, you can update all of them immediately by updating the ...


0

I think you cannot make an intersection of 2 or more tables inside a WHERE clause in uri.setDataSource(). You are supposed to query only the table that you are adding.


0

1) never use geometry (inlcuding point) for joining. Too many floating point precision issues with that. 2) I see your geometries are MULTILINESTRING, bad never use this for routing your the_geom should be of type LINESTRING - in PostGIS 2.0 ST_StartPoint / EndPoint will return NULL when applied to MULTILINESTRING and its just generally slower since ...


3

Load your shapefile(s) in as per instructions from http://suite.opengeo.org/docs/latest/dataadmin/pgGettingStarted/shp2pgsql.html Then query using something like SELECT sum(pointA.fieldB) FROM pointA, polygonC WHERE ST_Within(pointA.the_geom, polygonC.the_geom) AND polygonC.id = ?


0

This doesn't directly answer your questions, but some time ago I wrote a basic code to do what you're looking for. It extracts the orientation and inclination of RoofSurface polygons in CityGML, to estimate the solar irradiation of rooftops. The code is released on Github, so you might want to have a look. The part of the code that would probably interest ...


1

Try setting the SRID like this: ALTER TABLE streets_nordeste ALTER COLUMN geom TYPE geometry(linestring,3857) USING ST_SetSRID(ST_GeometryN(geom, 1),3857);


0

It's possible PostGIS is doing something under the covers that you need to do by hand in Shapely. We use prepared geometry automatically in predicate loops, and in Shapely you might need to marshall that on purpose. Depends on what your process actually entails. Advantages of python are the lower install overhead and the library of potential other features ...


1

It seems that 'shp2pgsql-gui' is now part of the postgis-gui package Try : sudo apt-get install postgis-gui It worked for me.


0

There are several issues mixed into your question. First, PostGIS can store geometries in the geometry column in any of thousands of CRS. The "Spherical Mercator" 3857 is a projected CRS, popular in web mapping. And the well known WGS84, epsg 4326 is an unprojected (geographic, long/lat) CRS. As for importing geojson to PostGIS, have you tried the straight ...


0

You do not need to use ST_Transform because you have already set your map projection with ST_SetSRID, so the SQL query would be like this: UPDATE new_dataset SET the_geom = ST_SetSRID(st_makepoint(longitude, latitude),4326) But if you want to use ST_Transform, because you want to visualize your query in Web Mercator or other projection this is the ...


0

Something like this should work and give you good performance. CREATE TABLE near_fiber AS SELECT table2.polygon_id, table2.nearest_dist, table2.nearest_name FROM table1, LATERAL (SELECT DISTICT ON (polygon_id) polygon_id, ST_Distance(table1.geom,table2.geom) nearest_dist, table2.name nearest_name FROM table2 WHERE ST_DWithin(table1.geom,table2.geom,2) /*...


0

Try out: UPDATE table_1 SET area = XXX FROM (SELECT ST_Area((ST_Within(table_2.geom, table_1.geom))) AS XXX from table_2 or more where conditions) mysubquery; I had same problem, but I solved it in this way.


1

Easiest way is to reference the table twice and use different conditions. One condition with the distance and one with the polygonid you want to have calculated. The result you safe as a new table. You get a new geometry with the objects within your search distance(plus the chosen feature) and the distances as a column. CREATE TABLE polygon1 AS SELECT ...


0

You have several issues with your query. First: ST_Within only returns true or false, no geometry. ST_Intersection will give you the intersecting geometries(the portions you want to have). Also the syntax of SET is wrong (SET is also no constraint, try to read some database basics, and also in the documentaion are several examples). Second: You are trying ...


0

If I understand your question correctly, this UPDATE statement will do what you want: UPDATE table_1 -- For each row in table_1... SET area = ( -- set its area... SELECT SUM(ST_Area(table_2.geom)) -- to the sum of the areas of the geometries... FROM table_2 -- of table 2... WHERE ...


1

If you want to take advantage of ST_DWithin, you'll need to specify a cutoff radius to limit the expansion of clusters. (It's possible to have a set of inputs where a user's cluster is the entire table). Say we have a user at (-80, 40), and we want to limit the search radius to 15km. That can be done with this modification: SELECT row_number() over () ...


0

Based on: SELECT generate_series(161794, 161797, 3); generate_series ----------------- 161794 161797 (2 rows) you can use the many to many signature of pgr_dijkstra: http://docs.pgrouting.org/2.2/en/src/dijkstra/doc/pgr_dijkstra.html#dijkstra-many-to-many WITH my_source_series AS (SELECT generate_series(161794, 161797, 3) AS ...


1

If you look at the docs for cursor.fetchall you will see that the return value is a list containing many tuples, each tuple representing a row in the returned query (cursorset). So, in order to test for that a return value [(True,)] is true, you need to use double indexing, [0][0], once to return the first element of the list, and once to select the first ...


4

In the window where you select your table from your postgisdatabase, you have to doubleclick the layer Then a query-window opens where you can define what features you want to load from your table


2

I am not sure I really understand what you are doing. How can a point cluster be a collection of linestrings? , but .. Remember you are using a spatial database, why not use the functionality in PostGIS? from a bunch of points you can collect them and use ST_Centroid on the collection. Or if it really is a collection of linestrings that you want to get ...


0

Deduce what is not visible. Suppose you stand at a vertex on the beach, looking at two remote vertices of a neighboring polygon. Then you can assume that any vertex in the whole sector behind these vertices is invisible from this vertex.


0

ArcGIS, assuming you are browsing the data, doing edits, analysis and what not, needs virtually "continues" access to the database. Each zoom or pan request will trigger SQL statements send to the database to get the latest results. You are live on the database with ArcGIS Server (ArcSDE). If your administrator is not familiar with (ESRI) GIS and is ...


0

The problem is maybe with the type of your geometry as you see its type is Polygon Z in ArcGIS but you are trying to convert it as polygon to use it in PostGIS. I did some search and found this workaround: run Feature Class to Feature Class tool then (before executing it) in 'Environments' at the bottom > expand Z values > set 'Output has Z Values' to ...


1

You might consiider PostGIS FDW (Foreign Data Wrapper) - it will connect to remote files such as Shapefiles or CSV, and they will appear and be queryable just like 'normal' PostGres tables. I think you'd need to use the GDAL/OGR FDW library - it supports shapefile connections. But don't take my word for it: http://postgis.net/2014/12/19/postgis_fdw/


2

You're missing a GROUP BY to specify aggregation of the rows. WITH clusters AS( SELECT row_number() OVER () AS cluster_id, unnest(ST_ClusterWithin(table.geom, 250)) AS gc FROM table) , unclustered AS( SELECT cluster_id, unnest(gc) AS geom FROM clusters) SELECT cluster_id as gid, string_agg(table.amenity, ';' ORDER BY table....



Top 50 recent answers are included