New answers tagged

0

you are trying to join 4 different geometries into a single shapefile, remember that the shapefiles only accept 1 kind of geometry (only points for instance). Also, PostGIS keeps a record of the tables geometries in the table 'geometry_columns'. I recommend: Create a new table with the specific geometry (for instance Point): CREATE TABLE newtable (...


3

One way is to use a UNION, not a join, which is what you have. UNION just concatenates the results from each query into a single result. So you'd do something like, WITH buffer AS ( SELECT ST_Buffer(ST_GeomFromText('POINT(3056563.45195278804692.195990143)',48402),1000) AS geom ) SELECT table1.geom FROM table1 WHERE ST_Within(table1.geom, buffer.geom) ...


0

The odbc_fdw extension is an optional component for CARTO, which should work fine without it. It requires PostgreSQL 9.5 so you would not be able to use it with 9.3 anyway. But the error your experiencing should be prevented by this: https://github.com/CartoDB/cartodb/commit/8c3a6e41 Can you check your code in db_service.rb for the presence of rescue ...


1

Paolo Corti provides an excellent answer on the postgis mailing list Use ST_Segmentize and then ST_DumpPoints, like this: SELECT ST_AsText((dp).geom) As wkt_geom FROM ( SELECT ST_DumpPoints( ST_Segmentize( ST_GeomFromText('LINESTRING(1 30, 15 30)',28992), -- this is the defined distance 1 ) ) ...


0

After a lot of searching on the google and readings I finally workout why I have the black area. The tifs I have are using pct color band,so I have to convert to RGB first, by using : pct2rgb.py -b 1 m697.tif ./m697_rgb.tif After this process I all the tifs can set the empty area as transparent by using : gdalwarp -dstalpha 1.tif 2.tif 3.tif warp_123....


2

Over at geofabrik you can download up-to-date shapefiles with selected osm attributes. They have even have categorized per country. Once downloaded and sorted the data that you need; you can upload your data to your postgis using a variety of tools depending on your project: Qgis, gdal, shp2pgsql and others. Also take heed, the data are in WSG84 (4326) so ...


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


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


2

OGR method, courtesy of this page: from osgeo import ogr conx = ogr.Open('PG:dbname=my_db user=postgres password=12345678') sql = 'SELECT * FROM table_name LIMIT 10;' for row in conx.ExecuteSQL(sql): print row.GetField(0) #gets first column, usually the id Another way: Adapted from here. Doesn't use ogr but it does let you access the data a bit more ...


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


1

The unit of measure for 900913 or EPSG:3857 is not a meter. Please do not use this projection for measuring distances. This is a sphere-based Mercator projection of ellipsoid coordinates, which throws conformality out the window. The unit of measure is technically a "non-Earth" meter, and the map gets stretched as you move further away from the equator. ...


0

You'll need to pass in the SRID to ST_GeometryFromText SELECT ST_DISTANCE( ST_GeometryFromText('POINT(980421.735630649 6997024.59849449)',900913), ST_GeometryFromText('POINT(1113063.06112784 7085437.74331884)',900913) ); The result will be in meters, albeit distorted. You can use this statement to ...


2

900913 was the Google Mercator SRID. The new one is 3857. The spatial_ref_sys table in my installation of PostGIS no longer contains 900913, only 3857 remains.


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


4

If you're happy with the characteristics of your projection, you should just use ST_Distance(geometry, geometry). In your example you seem to be in UTM, which is a pretty good projection, so why not? It's much much less CPU intensive than the geodetic functions ST_Distance(geography, geography). (Note that ST_Distance_Spheroid() just calls into the same ...


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


2

Rebuilding from source worked for me also. The problem wasn't realy system upgrade. Simply, creating extensions such as postgis_topology or postgis_tiger_geocoder threw exactly the same error. I advise also restarting postgresql service after rebuilding, just in case. Or better : -stop the service; -rebuild; -start again the service; And it might be ...


0

Just tried QGIS 2.14.4 and DB Manager seems to keep in memory last field names selected for Column with unique values and Geometry column parameters! The solution is simple then: upgrade!


1

You created a geometry colum with SRID 4326 but then you are trying to put in a polygon that does not identify an SRID. You just need to pass the polygon to ST_SetSRID() like this: UPDATE table_name SET column_name = ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(lon1 lat1, lon2 lat2 ... lon1 lat1)')),4326) WHERE gid=26;


3

TESTED USING THE LRS PLUGIN VER 0.3.6 Your problem may be that you need to update your plugin/QGIS version This is from the changelog of the LRS plugin: changelog: 0.3.6 - Fixed issue #6 (PostGIS data types not supported by memory provider) Your point files needs to have an attribute for distance along the line. It also needs a route ID field. Your line ...


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


2

Try to disable the "Support on the fly geometry simplification" parameter in the postgis store configuration in GeoServer. And make sure your polygon stays within the "world" boundaries, best if it's not touching the datelines. Also disable "advanced projection handling" in the WMS settings


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 = ?


1

Let me copy-paste from the Leaflet tutorial on WMS: The base WMS URL is simply the GetCapabilities URL, without any parameters Do not add any other parameters to the base URL. If you need extra parameters, pass more options to the L.TileLayer.WMS constructor instead. Judging by your code, I'll bet that the URLs actually requested have duplicated ...


0

Discussion in this thread and research in the code permitted to understand the difference between the two bearing calculation algorithms : st_azimuth of Postgis consider the Earth as a spheroid The angle calculation from Google Earth considers the Earth as a perfect sphere. It's a very simple formula : d=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(...


1

First, when you load the data using shp2gpsql-gui, ensure that you've set the SRID on the data correctly: you have determined your data are EPSG:3763, so use that number in the SRID field of the GUI. Now that your data is correctly loaded, you can get back geographic coordinate by using the ST_Transform function to convert them from their local system into ...


0

Wow, that long query is really necessary? What's wrong with: SELECT 1 gid, ST_Difference(ST_Union(lparcels.geom), ST_Union(luse.geom)) geom FROM boundary_test.land_use luse, boundary_test.land_parcels lparcels Or if you really need single part geometry: SELECT (dump_geom).path[1],(dump_geom).geom geom FROM (SELECT (ST_Dump(ST_Difference(ST_Union(...


1

If efficiency isn't a huge concern you could first make a GEOMETRY (which won't perform any bounds checking). Then compute its bounds. If the bounds are good, cast it to GEOGRAPHY. If not, raise an exception. You could encapsulate that logic in a function like this: CREATE OR REPLACE FUNCTION CastToGeographyWithBoundsCheck(geom GEOMETRY) RETURNS ...


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

Instead of the CARTO SQL API, you can insert your geojson sample to a CARTO column value (geojson_data, for example). And then use the PostGIS ST_GeomFromGeoJSON and set the_geom to the result: UPDATE name_table SET the_geom = ST_GeomFromGeoJSON(geojson_data) You will end up with this dataset/map.


0

My first recommendation would be to not do it this way, interpolation is heavily affected by edge conditions and resolution, by using a WPS on each map request you risk getting anomalies in your interpolation result every time the user moves the map or zooms in or out. Secondly, good interpolation is processor hungry so you will be placing a significant ...


0

The addresses styles are all defined in addressing.mss. Those particular ones are housenumbers and the style is #housenumbers { [zoom >= 17] { text-name: "[addr:housenumber]"; text-placement: interior; text-min-distance: 1; text-wrap-width: 0; text-face-name: @book-fonts; text-fill: #666; text-size: 9; } } From this ...


0

Are your points in Europe? If so, then you've just transposed the coordinates, so try: SELECT degrees(ST_Azimuth( ST_SetSRID(ST_MakePoint(-3.554562, 48.718544), 4326), ST_SetSRID(ST_MakePoint(-2.95284, 48.116875), 4326) )) AS degA_B;


2

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


1

I've built an R package called dggridR which wraps around the DGGRID software that Craig suggests in their answer, making it easy to compile and work with. It may be useful to you in exploring different grid possibilities. An example of choosing random cells and plotting them is available here.


1

The quickest way is to keep the geometry in hex-encoded WKB. import psycopg2 from shapely import wkb conn = psycopg2.connect('...') curs = conn.cursor() shps = {} # key: gid, value: Shapely geom curs.execute('select gid, geom as geom from pysal.stl_hom;') for gid, geom in curs: shps[gid] = wkb.loads(geom, hex=True) There's no need to use GeoJSON, ...


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.


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


0

Couldn't you just run an erase and end up with any stray line segments or add an erase to your script. Could you just covert your land use polygon to a polyline and run an intersect to see which areas actually overlap?


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


0

Answering my own question here that I posted here a while back... Another option that I found online (in addition to the suggestions above) is to use pgdbf (https://github.com/kstrauser/pgdbf) which dumps the SQL script that you can then use to ingest into postgres manually.



Top 50 recent answers are included