Tag Info

Hot answers tagged

7

If you are working on your workstation it's more a matter of taste. Knowing how to use psql is useful for some situations like running sql scripts from files, pipe it with other tools, etc. It depends on your needs. My everyday work is done using pgAdmin and I only go down to the CLI when needed. On the other hand psql is sometimes your only option when ...


5

You no longer need to use the templates or even the script in the new versions of Postgis. As you can see in the Postgis - installation page, all you need is run the CREATE EXTENTION command. -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION ...


4

Your problem is that you have added your data using Postgres data type Point, http://www.postgresql.org/docs/9.3/static/datatype-geometric.html Rather than the PostGIS geometry, or Geography type http://postgis.net/docs/reference.html#PostGIS_Types GeoServer has a PostGIS Data Store connector, rather than a Postgres Data Store connector. Good Luck


4

What do you mean by line = length? I guess what you mean is that your subquery will find the id of the shortest line. Then you ask in the outer query for a line with that id and some number less than 500. If the shortest line then have a a number that is more than 500 you will get nothing back. EDIT Something like this should work if I understand you ...


4

Just change the second line RETURNS geometry(Point, 4326) to RETURNS geometry The "type modifier" is a 2.0 feature. You might have other problems if GeoAlchemy expects PostGIS 2.0+, but for this particular function, just removing the typmod syntax should suffice.


3

First, never swallow an exception, or catch all exceptions. Your connection code should be more like: import sys import psycopg2 conn = None try: conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'") except psycopg2.DatabaseError, ex: print 'I am unable to connect the database: " + ex ...


3

According to www.postgis.org/docs/ST_Collect, ST_Collect — Return a specified ST_Geometry value from a collection of other geometries. (emphasis mine) but it seems your location column is of type geography. Perhaps you need to convert the geography to a geometry via casting: St_Collect (location::geometry)


3

I suggest to use a recent version of osm2pgsql. Openstreetmap node numbers have crossed the 2^31 bit boundary for integers, which needed to rewrite almost all software dealing with node numbers. Your max node is just beyond the limit, so i assume newer points get mistreated into a single point at 0°/0° in the Atlantic Ocean.


3

Your question probably requires a much more complex answer than this, but I'm just going to say that you may be able to use the following query to get the data you are looking for, to use the ST_OffsetCurve method: SELECT ST_OffsetCurve(theGeom, 2, 'quad_segs=4 join=round') AS theOffset FROM ( SELECT (ST_Dump("Shape")).geom AS theGeom ...


2

I think if you set up a PostGIS view that created a buffer on points in the layer you're editing in QGIS, and then queried the vector features the layer you're trying to query, you could do this. A great example is the blog post and video by Tim @ Linfiniti found here


2

To better understand your problem, first I have imagined the bellow situation: And these are the lines and the polygons tables: Also, I've tried to visualise the result, based on your requirement: to find out which line is how long within a surface: The above images helped me to build the necessary query: SELECT a.id_0 gid_line, a.name line_name, ...


2

try to order your result by sequence of your routing table. by doing that you will get your road segments in right order this is what i am doing this: SELECT pt.gid, seq, id1 AS node, id2 AS edge, cost, geom as line FROM pgr_dijkstra('SELECT gid as id, source::int4, target::int4, shape_leng::float8 as cost FROM my_table', ...


2

With the advice of Mapperz I've edited my query: SELECT DISTINCT ON (lnumber) lnumber, strahler, min(ST_Distance(ST_ClosestPoint(strahler_streams.geom, landslide.geom), landslide.geom)) AS distance FROM landslide, strahler_streams WHERE ST_DWithin(strahler_streams.geom, landslide.geom, 2000.0) GROUP BY lnumber, strahler ...


2

I just figured out that one of my earlier approaches would have worked, but I had a typo in my postgres query. Right-click and copy the binary hex string from SQL Server Management Studio. Paste into a string in the PG Admin III SQL Editor window. Delete the "0x" at the beginning of the string. Wrap that value with a decode. Now ST_GeomFromWKB has WKB. ...


1

It's important to understand that you cannot use more than one index into any one table at one time, and that indexes on columns unrelated to queries are of no use. This is why building an index on 'uidn' is of no use on an ST_Intersects query that involves a 'the_geom' column. One thing you can do to improve the efficiency of a spatial index is to ...


1

Hmm as I recall there should be in an sfcgal output in your postgis_full_version() output. Did you compile postgis with sfcgal support? It's not enough to just have sfcgal installed. I see you are right the instructions in docs don't tell you how to compile with sfcgal support. I'll amend that. What you need to add is in your postgis configure ...


1

You don't need the API. If you want to be sure you run your query, just work from the table view. From some CartoDB documentation (without a direct link for some reason): SQL in CartoDB To perform an SQL query in CartoDB, first load the table from your Dashboard. Once you have opened your table, in the lower right corner of your table you will ...


1

This is really just a Python question. Use the csv module in Python to write your output (https://docs.python.org/2/library/csv.html) import csv records = [(1, 2, 3), (4, 5, 6), (7, 8, 9)] with open('somefile.csv', 'w') as f: writer = csv.writer(f, delimiter=',') for row in records: writer.writerow(row) print "Done Writing"


1

You don't need the ST_ClosestPoint function. ST_ClosestPoint only gives you the same point that ST_Distance calculates and uses internally. So what you are doing is running the same algorithm twice. If I don't miss something or just think wrong about it you are also doing things twice when you are both grouping and using distinct in this way. Distinct ...


1

Postgis way : (import polygon shape to postgis using shp2psql ) UPDATE points SET district = poly.district FROM poly WHERE ST_Within(points.geom, poly.geom) see ST_Within and i cant remember easy QGIS way , but its possible


1

As noted by Stoner, you are using Postgres point data type instead of PostGIS geometry(point) and geoserver doesn't work with Postgres point data type. If you are using PostGIS 2.1, a new feature introduced is casting from Postgres Type to PostGIS type. You should be able to do this with the command ALTER TABLE realestate ALTER COLUMN coords TYPE ...


1

So answer to question, no there is no way that i know do to do this in QGIS without creating custom python plugin (but i know postgresql better than qgis) for postgresql, here some tips CREATE OR REPLACE VIEW lines_touching_buffer AS SELECT * from lines l WHERE l.geom && ST_GeomFromEKWT(insert your ewkt here) ; That creates view which displays ...



Only top voted, non community-wiki answers of a minimum length are eligible