Tag Info

New answers tagged

3

ST_Collect is probably not the function you are looking for, as this simply combines geometries into a geometry collection of some type, and does not actually union/dissolve them. ST_Union, on the other hand, does dissolve overlaps, and assuming polygonal input (which is most probable in conjunction with ST_Buffer and an input table called point), and ...


0

Leaflet is certainly a good option, out of the box leaflet does not support kml but if you add a plugin like omnivore you should be able to get things work reasonably simply. Perhaps have a shot and post your code to jsfiddle if you get stuck then someone can provide more assistance. Regards, Rowan


0

I don't have a fully worked out answer, but maybe enough to get you started. These functions might help: ST_Line_Locate_Point() gives the distance as a portion of the total line length of a point along a line. ST_Line_Interpolate_Point() returns a point geometry for a point at a given distance (again as a portion of the total length) along a line. What I ...


1

Currentyly there is a method to filter geometries within some distance in PostGIS that uses spatial index: http://postgis.net/docs/ST_DWithin.html


0

The main thing is getting a correct connection string, so you're doing the right thing playing w/ ogr_fdw_info until you get a connection working. "MSSQL:server=localhost\SQL2012Express;database=ProSpatial;trusted_connection=yes;" Here's an OGR connection string I found online, and there's another on here at GIS.SE, Shapefile to MSSQL with ogr2ogr fails ...


1

What you have in PostGIS for that is ST_Snap http://postgis.net/docs/ST_Snap.html This is a four-vertex polygon POLYGON (( 420 420, 420 520, 700 520, 700 420, 420 420 )) This is an adjacent five-vertex polygon that has one vertex in the middle of the common boundary at location (582 420) POLYGON (( 420 420, 582 420, 700 420, 700 320, 420 320, 420 420 )) ...


0

The GPS data format does not know polygon features (only points and lines), so you will not be able to import and edit them. I suggest to use a shapefile format or spatialite database for off-Postgis-use.


0

Qgis has got an informative error in the status bar for all the layers being loaded. Try to read what the error says after loading the layer. Another source of error could be that your I'd column is not unique . Test to see if it that is the case


0

Here's a post from Paul Ramsey (while at Boundless) about creating overlays: http://boundlessgeo.com/2014/10/postgis-training-creating-overlays/


0

It seems that there was a problem with the data. I've loaded another dataset, and the spatial functions runs ok both in geography and geometry types.


2

For any geometry the units are determined by the spatial reference system. Which you get for a particular geometry with functions ST_SRID(geom). http://postgis.net/docs/manual-2.1/ST_SRID.html (these numbers are in the spatial_ref_sys table and you have to look at the proj4text to see the units description). For geography type coordinates are always x ...


0

It seems that it is not possible to restrict Layer->Add Layer->Add PostGIS layers to a subset of the schemas (schemae? TNIRN) except if that subset is schema = public. The restriction to only load tables in the public schema, is in the connection information ("only look in the public schema" checkbox). The solution I have hit on is to copy the table/s ...


0

From the GDAL PostGIS Raster driver documentation, last updated February 2014: write support to GDAL PostGIS Raster driver is under development The best available tool is still raster2pgsql on a local GeoTIFF file.


3

select * from eating_drinking where ST_GeometryType(way)='ST_Point'


0

Perhaps try the package 'RapostgreSQL'. I'm not sure, if it will allow you to write spatial data to PostGresql, but you can try.


2

Are you in the USA? If so, determine the State Plane coordinate system that is used by your area, and what the SRID is, for example, What is my State Plane zone in Colorado? I suggest using State Plane as the units are FEET, which can easily be converted to miles. I use SRID/EPSG:2877 So now use an ST_Transform statement which will project your data ...


0

I think that error might happen if your proj files used by GDAL can not be found. Alignment relies on that as I recall. What does: SELECT postgis_full_version(); return. If it returns GDAL_DATA can not be found, then that's the problem. Since that is where it looks for the coordinate reference files.


0

You can use a window function over your data : select distinct (ST_Extent(geom) over ())::geometry box from tableName category='airport'; Also since you tagged openlayers take a look at ST_AsGeoJSON you might find it helpful


2

Just use ST_Extent for the sub-query that selects your airports. The only trick is that in PostgreSQL you must use an alias for the subquery. Look at "a" in the following example which gives the aggregated extent of the 5 first geometries of the table. select ST_AsText(ST_Extent(a.geom)) from (select geom from tableName limit 5) as a;


2

You could use St_Envelope on all your airports docs ! ST_Envelope — Returns a geometry representing the bounding box of the supplied geometry.


1

The fact that there is an SRID number (4326 in your case) coming after the type number needs to be flagged, since SRID's are an optional component of EWKB. The number you are seeing is composed of the type on the bottom byte and the "has an SRID" mask on the upper byte. https://github.com/postgis/postgis/blob/svn-trunk/liblwgeom/liblwgeom.h.in#L84


0

Your query finds the whole polygon that intersects with other polygons. I guess you'd rather want to have just a part of it (a point in your case). You might try with the following query: SELECT DISTINCT ST_Intersection(a.wkb_geometry, b.wkb_geometry) wkb_geometry FROM polygon_table a, polygon_table b WHERE ST_Intersects(a.wkb_geometry, b.wkb_geometry) AND ...


0

Unfortunately I have egg on my face with this one...although this was my first thought I ruled it out like an idiot due to passing in my parameters backwards...Thanks everybody http://stackoverflow.com/questions/29669045/postgis-st-distance-query-returning-inaccurate-results


0

Use postgresql as database, and install postgis extension, and enable the extension for your database. Then use a column similar to location GEOGRAPHY(POINT,4326) to store location. Create a index for the column using GIST index type, similar as: CREATE INDEX global_points_gix ON global_points USING GIST ( location ); Insert similar as: INSERT INTO ...


0

As the data owner cannot change the field types, I have used ogr2ogr and a -sql select statement to exclude the field that is causing problems. As the field, LENGTH, is derived data, then it shows up in QGIS anyway so I was ok to omit it. My new code is as follows; ogr2ogr.exe -f PostgreSQL PG:"dbname=postgis host=localhost user=username ...


0

Try using http://epsg.io/ For example, put the SRID after the slash: http://epsg.io/102642 There are Export links at the bottom to view different representations of the projections. The PostGIS Export shows the correct INSERT statement. You can also add .sql to the URL (e.g. http://epsg.io/102642.sql) to get the raw data.


0

Line direction is created by line draw direction, that is, how the line was originally imputed. i.e. from point a to point b, you then have a direction, if handled as a multi segment line the the individual nodes may all have different draw directions against the overall trend of the line. Modeled logical you could create oo rules for your data base that ...


0

There's a (commercial) desktop solution GeoView that creates a spatial database out of GPS-enabled videos and accompanying GPS tracks. The database then can be used to display recorded tracks and extract specific video frames along one. It also supports PostGIS RDBMS via FDO.


1

I suspect that your google SRID may be incorrect. I believe that google geocode results use SRID 4326 (WGS 84). I believe the google uses 3857 for the map display, but outputs the data as 4326.


0

The Oracle datastore factory is not found on the classpath. It should appear in the list after 'factories: POSTGIS'. I think you should have the following dependency in your pom: <dependency> <groupId>org.geotools.jdbc</groupId> <artifactId>gt-jdbc-oracle</artifactId> <version>2.5.5</version> ...


2

Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create geometries. Try the following, where you can replace the JSON in the top part: WITH data AS (SELECT '{ "type": "FeatureCollection", "features": [ { ...


0

Judging from the location of your point, I'm willing to bet that your data is in EPSG:4326 rather than EPSG:3857. Confusing latitude/longitude values with web Mercator X/Y values usually results in a point near the Equator/Prime Meridian intersection.


1

From the PostGIS manual, ST_Value() returns ... the value of a given band in a given columnx, rowy pixel or at a particular geometric point. Band numbers start at 1 and assumed to be 1 if not specified. If exclude_nodata_value is set to false, then all pixels include nodata pixels are considered to intersect and return value. If ...


0

Try setting project CRS first to EPSG:3857 with On-The-fly-reprojectionenabled, then add a layer from the openlayers plugin, then add your postgis datasource. If that does not work, please add the extent of the postgis layer, as reported by rightclick, Properties, metadata tab.


1

You could do the following: Find the nearest edge to A and B Then use ST_LineLocatePoint und you get a ratio of where the nearest point lies on that edge. With ST_LineInterpolatePoint you get the coordinates of each point on their nearest edge and with ST_LineSubstring you could also get the partial geometries, but that's not necessary for the shortest ...


1

Frankly no, I've read it through five times and all I see is too many subqueries, but nothing that should stop it from executing. The API should have some kind of error info embedded in the response, no? Is your table set public? The SQL API won't let you alter the query on a non-public table. SELECT d.cartodb_id, ST_MakeLine( ...


1

There are a few issues to consider. First, have you considered using a VIEW? I.e. CREATE OR REPLACE VIEW places_with_coods AS SELECT gid, ST_Y(geo::geometry) AS lat, ST_X(geo::geometry) AS lon FROM places; However, if you want to use triggers, these changes will make things work. The important pieces being that NEW is the row, which can be modified ...


0

Use new.geo instead. It should work.


0

I was able to fix the problem, and here's what was happening: It turns out there was nothing wrong to begin with, the layer was displaying exactly as I set it, and that was the problem. Because the layer above had such closely-packed grid polygons, I set the style to not draw any borders, otherwise when zoomed out you would just see a slew of black borders, ...


1

I would start by looking at openstreetmap.org. Why re-invent the wheel, right? If that's not exactly (e.g., streets missing or moved) what you need, you can contribute to make the OSM data better. Another source related to OSM, switch2osm.org. In addition to the above general resources, these specific links should help get you started: ...


4

If you have global data, use a geography type: CREATE TABLE points ( gid serial primary key, name character varying, point geography(Point,4326) ); CREATE INDEX points_point_idx ON points USING gist (point); Then use a function that can use the spatial index, using a metric distance (see ST_DWithin) SELECT name, point FROM points, (SELECT ...


0

Currently this can't be done natively, as far as I know. The thread here: Converting ArcGIS layer style to Styled Layer Descriptor (SLD) file? has some info on getting the styles from ESRI lyr to a sld, which is basically what you want, but it's not very smooth (or cheap for arc2earth). I think a more elegant solution is waiting to be developed.


2

If the area of the buffers should be combined (as not to count the same area several times) then this query should be used: SELECT ST_Area(ST_Union(ST_Buffer(geom, 50))) FROM points;


0

Judging from comments, you are looking for a simple geometry(point) intersects all the geometry on a table right? What you can use it should be something like this; select * from circles where st_intersects (circles.geog,(ST_PointFromText('POINT (48.732084 -3.459144)',4326))) Mind the SRID mixing problem. Because i gave 4326 to the selectiong ...


1

is the area field a float? you may want to try creating the area index (clustered or non) on an integer field (if your use case can accept the generalization).


1

Unfortunately, I have not found a method for creating SLD files in ArcMap for GeoServer use. There are some alternative app. options: Udig QGIS GeoExplorer GeoServer admin SLD editor


14

My guess is that you coordinate transformations have introduced tiny rounding errors (see an example below). As there is no way to set the tolerance in ST_Equals, this is causing ST_Equals to return false for some geometries that only differ in the nth decimal place, as the geometries have to be identical in every respect -- see the intersection matrix ...


2

Did you run ST_IsValid check on your geometries? If they are invalid, all bets are off. ST_Intersects and the other family of GEOS spatial relationship functions will often just return false because the area is not well-defined from an intersection matrix point of view. The reason doing ST_Buffer probably works is because it's converting your invalid ...


0

When I recently tried to save my tile-server update again, I found out that my main fault had been to forget about granting rights on the 'gis' database to the the www-data user -- which was necessary because I had run osm2pgsql with my user account. The switch2osm manual had also mentioned the necessary script in the right place. After all, the error ...


4

I guess you mean the area of the combined 50m buffers? If so, then like this: SELECT SUM(ST_Area(ST_Buffer(geom, 50))) FROM points; This assumes that the points are projected in a CRS with meters as units. And I'm not sure I understand what you mean by "points that form a line". Is the PostGIS table a point feature or line feature?



Top 50 recent answers are included