Tag Info

New answers tagged

3

ST_Polygonize will do the job: CREATE VIEW boundarypolygons AS SELECT g.path[1] as gid, g.geom::geometry(polygon, 31492) as geom FROM (SELECT (ST_Dump(ST_Polygonize(geom))).* FROM boundary ) as g;


1

Since you are using PostGIS and QGIS, you can try to convert them using both, to decide which one is best suited for your problem. To use PostGIS, the LINESTRING must be closed. You can check if they are closed with the query: select gid, st_isclosed(geom) from boundary; If the lines are closed, you can create another table to check the results, with: ...


0

My current workaround is to build a side table with 2 columns, the objectid and geometry( as PostGIS geometry). And to maintain that table with triggers on the featureclass. Then perform spatial queries against the side table and join back to the featureclass. I have an open incident with Esri, but so far, no luck. I will update this answer if/when we make ...


0

Use mukey to link the soils, not musym(the soil's name). If you are having continued problems, contact the specific state's NRCS State Soil Scientist for the assistance. They will have someone on staff that can help with more specific questions. When we make Hydrological group soil maps, we don't use the break downs from the Web Soil Survey(WSS). We ...


2

Might want to try HausdorffDistance. Suited for this kind of question. SELECT ST_HausdorffDistance('0101000020110F0000FE546AD2413E5EC16D56617C93C74FC1'::geometry, '0101000020110F00009D7E6384413E5EC10185DD7F93C74FC1'::geometry); Detailed here: http://postgis.net/docs/ST_HausdorffDistance.html And as explained at /wiki/Hausdorff_distance: Informally, ...


0

CartoDB doesn't support direct connections to user databases, so you'd need to use our SQL API in order to connect both: http://docs.cartodb.com/cartodb-platform/sql-api.html


0

Jan, Not sure when you downloaded that binary. In first packaging I made a mistake with the 9.3 build and left out the ODBC driver which is needed for the MSSQL connection. Someone alerted me when they tried to connect to MS Access and I since fixed the issue. Also if you upgraded PostGIS since, it may have overwritten the libgdal driver and the one that ...


1

The Soil Data Viewer from the NRCS makes querying single attributes from SSURGO data relatively easy.


0

You can also combine rgdal and RPostreSQL. This example function creates a temporary table with RPostgreSQL and sends it to readOGR for output of a spatial object. This is really inefficient and ugly, but it works quite well. Note that the query has to be a SELECT query and the user needs to have write access to the database. RPostGIS <- ...


2

I think that you have to configure postGIS library as extension in your database. Here an example.


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

osm2pgsql is surely designed to import the whole planet into postgis. You have to make sure to use a recent version of osm2pgsql, because the node numbers in OSM have hit a limit which made it necessary to change the number format from integer to long integer. Older versions of osm2pgsql will fail therefore. On windows, there are a few more pitfalls. You ...


0

Maybe osmosis is the option you are looking for. You can use osmosis to import all tags into the postgis database. Since osmosis does not build GIS ready to use objects you must do this in a separate process using SQL. A very good collection to start with are the osmosis layers from moenk available on github.


1

You could add row numbers to the points and then join points and non-spatial table based on row number and ogc_fid (assuming ogc_fid starts with 1).


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


2

This is a case sensitivity/quoting issue. "PointsForGpxExport" and PointsForGpxExport are not the same table name. PostgreSQL, per the SQL standard, case-folds unquoted identifiers. (It case-folds to lowercase, where the standard says uppercase, though). So when you write PointsForGpxExport, PostgreSQL treats that as the same as pointsforgpxexport. Since ...


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


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


3

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


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

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

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


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

I have found that if you use the reconcile versions tool from the data management tool box, you can reconcile versions without being in an edit session. You can just drag your database connection in to the Input DB connection. Perhaps that may be an alternative solutions to what you are looking for?


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

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

GIS functions and the geometry_column, spatial reference tables etc do not exist by default in PostgreSql. You need to install the PostGIS extension (which you can add to an existing instance of PostgreSql or add when you install a fresh instance of PostgreSql). Go to the PostGIS Getting Started documentation and follow the directions on how to CREATE ...


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


-2

Any drawing app that uses co-ordinate geometry formula should allow you to simply import your images and draw over the areas of interest. Consider SketchAndCalc.com for accurate area or perimeter calculations of irregular polygons.


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

Vector solution: Intersect or Union the two layers. Both functions should be available in all three of the softwares you have tagged. Note that Intersect returns only the areas of overlap, while Union returns both areas of overlap and areas that are one layer and not the other. Hence Union will allow your results to total 100% of inputs, while Intersect may ...


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


0

I've mitigated this issue by: Put the port number in the name of the connection file as soon as its created. Add a view that will report system info--the view will be visible in ArcCatalog, so you can see which port you are connected on: CREATE VIEW public.vw_sysinfo AS SELECT inet_server_port() AS inet_server_port, current_database() AS ...


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

This is tested with ArcGIS Server 10.2.2, QGIS 2.6.1 Brighton, PostgreSQL 9.2.9-3, PostGIS 2.1. I'm looking forward to know if this still works in ArcGIS 10.3 Basic. Follow these steps: Given a feature class in ArcGIS in an editable layer. With pgAdmin create a new database using "template_postgis_20" (let's name it 'mydatabase'). From ArcCatalog - ...



Top 50 recent answers are included