New answers tagged

0

libpq allows us to connect to Postgres/postgis for read write operations. If you have isnatlled Postgres,libpq is already available.It would be available insatllation path like C:\Program Files\PostgreSQL\9.5\include . //To make use in C/C++, you include these header files #include "libpq/libpq-fs.h" #include "libpq-fe.h" #include <stdlib.h> #include ...


0

You can have array columns in a table. For your example table the create statement would be along the following lines. CREATE TABLE spatial_table ( name VARCHAR(20), timestamps timestamp[], the_geom geometry )


0

You can generate a query that outputs GeoJSON objects with the ST_AsGeoJSON function. Here's a question and answer that explains how: SQL query to have a complete geojson feature from PostGIS


1

First, using <#> will only compare bounding boxes. If you have PostGIS 2.2 (or greater in the future) you can use <-> operator to get the real distance calculation. That means that after the index search a recheck is done to find out the real distance. Second, as John B commented, the knn-functions need a constant to work with. Since you are building ...


1

I think you're overcomplicating things here when you could use ST_MaxDistance, this will return the maximum distance between two points within two point clouds. WITH points AS( SELECT id, (ST_DumpPoints(lane.geometry)).geom as geom FROM lane GROUP BY id ) , min_distances AS( /*Finds the smallest distance for each distinct tuple ...


0

I would take OGR (ogr2ogr) and set the layer creation option COLUMN_TYPES: -lco COLUMN_TYPES field1=INTEGER,field1=FLOAT8. That's another layer creation option as Mike T suggested above. In general, I would consider switching to GeoPackage since Shapefiles are deprecated and have deficiencies like field names limited to 10 chars.


1

You cannot simply print a class. If you want to do so, you have to add a toString() method to it. See e.g. http://stackoverflow.com/questions/8555771/why-is-the-tostring-method-being-called-when-i-print-an-object or http://stackoverflow.com/questions/8001664/how-to-create-a-println-print-method-for-a-custom-class or ...


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.


3

The number width/precision is specified in the DBF file, as support by GDAL/OGR with get/set width/precision (i.e. see API). Looking at the documentation for the PostgreSQL / PostGIS driver, there is a PRECISION layer creation option: This may be "YES" to force new fields created on this layer to try and represent the width and precision information, if ...


0

For the second point you can use the Information_schema and select the needed columns. "SELECT column_name, data_type FROM information_schema.columns" and use this information in a loop with an ALTER TABLE statement together with SET DATA TYPE to change the types. This of course needs to be inside a loop for all tables which you also get from ...


0

You get a syntax error, try : UPDATE mad SET geom_p=CASE WHEN ST_CoveredBy(mad.geom,b.geom) THEN mad.geom ELSE ST_Multi(ST_Intersection(mad.geom,b.geom)) END FROM minimad mad, min b WHERE ST_Intersects(mad.geom,b.geom) AND NOT ST_Touches(mad.geom,b.geom); Also you can use the inner join: UPDATE mad SET geom_p=CASE ...


1

I started it all again: restored a previous database from backup; create extension postgis; create extension postgis_topology; ogr2ogr from console creates additional tables (like waypoints); after this I need to grant access in those additional tables to php/apache user.


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


0

Solution: Explicitly set the correct srsName gmlPolygon.getValue().setSrsName("EPSG:4326"); I also had to add srsDimension to the points since they had x, y, z: <ns1:pos srsDimension="3">32.23435 -110.34544 90.2</ns1:pos>


2

Similar to what Zoltan has said, but you want to group by the color, which will then get you the sums for each color. SELECT sum(ST_Area(ST_Intersection(grid.geom, affected.geom))), grid.color FROM grid, affected WHERE ST_Intersects(grid.geom, affected.geom) GROUP BY grid.color; The final WHERE ST_Intersects(...) will not affect the answer, ...


0

In PostGIS 2.2 with SFCGAL, this can be done with ST_StraightSkeleton or ST_ApproximateMedialAxis, depending on your criteria.


2

SELECT sum(st_area(st_intersection(grid.geom, affected.geom))) FROM grid, affected WHERE grid.color = 'green' and st_instersects(grid.geom, affected.geom);


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


2

Instead of a marker you can generate a buffer, setting each category (metro, tam and bus) to a numeric field (1000, 750 and 500). Then run the following queries: SELECT cartodb_id, ST_Transform( ST_Buffer(the_geom::geography, 1000)::geometry ,3857 ) AS the_geom_webmercator FROM table_name WHERE field_name ilike 'metro'; SELECT ...


0

You need to format the LINESTRING (path) in your nodejs server : Something like : LINESTRING(0 0, 1 1, ....) --> WKT FORMAT OR '{type : LineString, coordinates : [[0,0],[1,1], ...]}' --> GEOJSON FORMAT Now you should pass this to your query like this : SELECT m.* FROM merchants m WHERE ST_Distance(ST_MakePoint(m.longitude, m.latitude), ...


0

SELECT ST_GeomFromText( ST_AsText( ST_GeomFromGeoJSON('YOUR GEOJSON') ), 4326)


5

Your GeoJSON sample lacks information about the coordinate reference system, although you say it is SRID 900913. This is so-called Google Mercator or Web Mercator, which has been superseded by EPSG:3857, which I will use in this example. An important question is whether you really want Well-Known Binary, which does not include an SRID, or Extended Well-Known ...


6

Use ST_GeomFromGeoJSON select ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}') Result: ...


7

Use ST_GeomFromGeoJSON. SELECT ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}'); Returns: ...


2

By default shp2pgsql does NOT create indexes. You need to pass -I to make it generate a spatial index. http://postgis.net/docs/manual-1.3/ch04.html#id435762 Check if your table has an index by running \d tablename in psql. In the list of indexes should be a line with "gist" (unless you picked a different index) and your geometry column name. You can add ...


2

Could be, for example: SELECT kph, count(*) FROM data WHERE ST_Within( ST_Transform(geom, 2163), ST_Buffer( ST_Transform( ST_GeomFromText('LINESTRING (77.2089421749115 28.5225030428734, 77.2088992595673 28.5223333626164,77.1612417697906 28.5640959203597)', 4326), 2163), ...


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.


2

ogr2ogr/ogrinfo are great tools...but also...PostgreSQL provides two built-in tools for exporting (pgsql2shp) and loading (shp2pgsql) shapefiles. They are easy to use and well documented...see the docs and this cheat sheet. Quoting the PostGIS 2.0 docs: An example session using the loader to create an input file and uploading it might look like this: ...


2

So far I could think of computing the bounding box and getting its maximum Y value like in: order by ST_YMax(Box2D(geom)) PS: Actually the conversion to a box is not necessary and to sort from north to south desc works like a charm, hence the final query part is order by ST_YMax(geom) desc


3

YES The -spat switch is completely optional, and the typical use case is probably not to use it. I never use it, because when doing a shapefile import I have never needed only part of the shapefile (or I would rather get it all into PostGIS and then do a spatial selection there). ogr2ogr will run and import all features in the shapefile. Your statement ...


4

If you want to transform coordinates to another SRID, you will use ST_Transform: SELECT ST_AsText( ST_Transform('010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141' ...


1

You can use the ST_AsText function to get the WKT representation of your geometry, this will return the coordinates in whatever projection you have. (You will only get the latitude and longitude if your data is stored in a Geographic Coordinate system, SRID 4326 works for most cases) You can use this query to change SRID ALTER TABLE table     ALTER COLUMN ...


5

That output is hex-encoded EWKB, or extended Well-known binary, with a specification provided here. This example is identical to ISO WKB, since it does not have an SRID or use dimensions beyond 2D (such as Z, M, or ZM). The formats are different otherwise. You can decode geometry data (i.e. longitude and latitude) from WKB using a wide range of tools ...


3

A similar idea to Underdark's, but doesn't require a unique field Adding a unique id is easy with attribute editor and $rownumber, but it can be slow if you're using shapefiles and have lots of points. Use Rule Based Rendering, and have two rules randf(0.0,1.0)>.9 [default rule] Unselect the default rule so it doesn't appear, and symbolise the first ...


2

Here an example with ST_GeomFromGeoJSON() : select ST_GeomFromGeoJSON ('{"type":"Polygon", "coordinates": [[-74.54635620117188,40.773261878622634], [-74.69741821289062,40.61082491956405], [-74.30740356445312,40.61603737424185], ...


2

Your incoming geometries are multipolygons, while the datatype of the geometry is polygons. These are incompatible, and hence you should convert the data type of your column to multipolygons, so that you can save the valid geometries. This can be achieved by the following command: Alter table table1 alter column geom type geometry ( multipolygon,srid)


0

Alter table table1 alter column geom type geometry ( multipolygon,srid)


0

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


2

Other way, if no field suitable for modulo filter, is create a cluster layer. There is a plugin: https://plugins.qgis.org/plugins/QgisMarkerCluster/ Then use Scale Dependent Rendering...


4

Your dataset needs some kind of feature ID, like in this example: Then you can use a rule using the modulo operator % to fhe features, e.g. every 10th feature only: <theID> % 10 = 0 This is the corresponding rule-based renderer. You can add scales to the rules as well.


4

Try to update your script and use ST_AsGeoJSON(). You can also choose the number of digits with this function. http://postgis.net/docs/ST_AsGeoJSON.html


0

You need to check the value of the sequence. QGis use the next sequence number but you have no guaranty that value isn't already used in the table. If the sequence is being used for unique ids in a table, you can simply do this: get the max value of the field: select max(gid) from mytable; Update your sequence: SELECT setval('bo_gid_seq', the_max_value ...


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


2

Use ST_SetSRID and ST_Force3D (or ST_Force_3D) functions to force these geometry types. To assign these as defaults for each geometry in a table, you could use a trigger function that evaluates the geometry before it is added in the database. CREATE OR REPLACE FUNCTION force_geom_z_4326() RETURNS trigger AS $BODY$BEGIN NEW.geom = ...


1

Putting it all together for those who are interested CREATE OR REPLACE FUNCTION foo.map_api() RETURNS trigger AS $BODY$BEGIN NEW.link:= 'http://www.openstreetmap.org/#map=17/' || ROUND(ST_X(new.geom)::NUMERIC,0)::TEXT || '/' || ROUND(ST_Y(new.geom)::numeric,0)::TEXT; RETURN NEW; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION ...


1

One way that you could get this to work and speed up the query would be to "region tag" the geometries of the points table first. This would look something like: ALTER TABLE trips ADD COLUMN start_county_id char(5) -- assuming this is a fips code ADD COLUMN end_county_id char(5); UPDATE trips SET start_county_id = a.geoid FROM us_county AS a WHERE ...



Top 50 recent answers are included