Tag Info

New answers tagged

1

In general, there is more support for the geometry than the geography data type, see function comparison matrix. For your type of query, classic point in polygon, I think you can use the geometry datatype, setting the spatial reference ID (SRID) to 4326 (lat/lon). See, this thread on the difference between geography and geometry for more info. The gist ...


0

You can try with ogr2ogr, if your db encoding is UTF8 use this command : ogr2ogr -f "ESRI Shapefile" c:\shape.shp PG:"dbname=db_name user=user_name password=xxxx host=serveur_192.168.1.1 port=5432" -sql "select table_id, table_name, geom from table" -overwrite -lco ENCODING=UTF-8 You just need to change these variables : db_name, user_name, xxxx, ...


0

The umlauts are not lost. You are looking inside your Postgis table with UTF-8 encoding, and into the .dbf table with System encoding. If you open the .dbf file in Libre Office, you will be asked for the encoding. Select UTF-8 and the content is readable. You can load the shapefile (or the Postgis table directly) into QGIS, setting encoding to UTF-8 or ...


3

I think you want to exclude the intersection of the buffer in the where clause. WITH subq AS ( SELECT p.id, p.name, unnest(ARRAY(SELECT q.name FROM w_point q WHERE p.id != q.id AND NOT ST_Intersects(q.geom, ST_Buffer(p.geom, 0.1)) ORDER BY ST_Buffer(p.geom, 0.1) <#> q.geom LIMIT 5) ) as name FROM w_point p ) SELECT ...


4

You can simplify the whole thing by using coalesce, which selects the first non-null item in a list, in this case, either the nearest point to you search point, (28.959495,41.019913), or the point itself. with input_geom (geom) as (select st_setsrid(st_makepoint(28.959495,41.019913), 4326)) select coalesce( (select st_closestpoint(pts.the_geom, ...


0

This is sort-of the same answer as this, except adapted a bit more for your situation. Basically, you are mixing SQL parameters in WKT. Since you have numeric data, you shouldn't be making WKT anyway, and directly make a point instead. String sql = "INSERT INTO points (gid, osm_id, name, type, geom) " + "VALUES (?, ?, ?, ?, ...


1

This: 'POINT(?, ?)' is within a literal string, so placeholders are not interpreted. You must instead keep the placeholders outside a literal and use concatenation or function call syntax. In this case there's actually a point(x,y) function, so you can just write: POINT(?,?) without the single quotes, but if that weren't the case and you needed to ...


0

Was dealling with same problem just few hours ago. I think you should add a CIDR mask length (/32 for a single address - Wikipedia details) in ip-address section: # TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all ...


1

I believe adding the osm user name or osm user id is out of the design scope of imposm. I don't know if the design goals are any different for the rewrite of the tool in Google's go language. Imposm wants to be fast at helping you create maps and does not appear to be interested in other data that may be used for statics or does not add to map data. Most ...


1

Try turning off the automatic simplification for the layer: Right click the layer and select "Properties" Click the "Rendering" tab Uncheck "Simplify geometry" This feature can cause issues with certain projections, and I suspect that's what you're encountering.


1

Just wish to add two things to John Barça's fine answer: First, ST_PointFromText() would be most useful if your coordinate data were already in the form of lines of text like this POINT(xxx.xx yyy.yy) where each xxx.xx yyy.yy were actual coordinates, perhaps as output from another process/system. Going out of your way to add in the text "POINT" is ... ...


4

First of all, some performance metrics, comparing the two different ways of producing points for a random selection of a million points. create table test (id serial, x real, y real, geom geometry(POINT, 27700)); insert into test (x, y) select random(), random() from generate_series(1, 1000000); update test set geom = ST_SetSRID(ST_MakePoint(x, y),27700); ...


2

I clearly don't know the "best" solution between ST_PointFromText and ST_MakePoint. The thing you should notice is that ST_PointFromText and PointFromText are the same function (syntax change due to function normalisation SQL MM). Now, the right ones are ST_* So what you want is something like below (|| are for concatenation) UPDATE points_of_interest SET ...


1

In pseudo code: union (A, B) = combination of difference (A, B), difference (B, A), intersect (A, B) In other words, the union of two regions is the combination of their symmetric difference and their intersection. To actually do the combination, use ST_Collect() which is in general orders of magnitude faster than ST_Union because it ...


3

It should be something like this: select s.state, count (r.state) as neighbors from USstates as s, USstates as r where st_touches (s.the_geom, r.the_geom) and s.state != r.state group by s.state


1

I think the two messages are separate. The first message is just a warning that anything such as formulae, formatting and additional worksheets cannot be saved in a CSV as it is a simple ascii format. The second message suggests that you maybe have a text field as one of your attributes which contains a comma. This will effectively introduce an extra ...


0

Do you need to care? I looked at CreateTopoGeom documentation. If PostGIs cares about the order, then they will take care of it. Oracle Spatial is very picky about the order of the points in a line and the rings. If you run the function on the data that you have, and if you display that data baring any SRID conversion issues, then you should be good to go


3

I ran into the same problem and I ended up using adopted way_area size clauses. By default they seemed to be too small for me. So when zooming out it would fetch so many very small polygons that that was too much data to transfer. So basically you don't display too small polygons when being zoomed out. You have to try and error until you have the best ...


2

When I looked at your question I thought you might have a conflict between postgresql versions and postgis versions. That made me reach for the Postgresql to PostGIS supported versions matrix. But that is not the problem. The problem is with your apt-get line apt-get install postgresql postgis postgresql-contrib postgresql-9.1-postgis ...


2

ST_StartPoint() only accepts LineString as input. You'll have to strip your table down from MultiLineString to LineString, either by taking only the first element up each geometry ALTER TABLE foo ALTER COLUMN geom TYPE Geometry(LineString,4326) USING ST_GeometryN(geom,1) Or, more correctly, dumping the multis out, in case there are legit multis. ...


0

I don't know why SPIT is not working for you and it may be a version thing (either with QGIS or PostGIS). It could also be that you don't have the correct access permissions on your account on PostGIS and you need to take that up with your database administrator (I'm assuming the PostGIS instance is remote too). However, what you could do as a work around ...


0

Geotools is a powerful library, and can read from PostGIS, for example, and can export to ShapeFile, for example, using the "Feature" object. I cann't show you a complete "example", but it's easy to find out a way to do it, in Google.


1

How can I pass the value from the HTML/JavaScript to the PHP file where I have the connection with database? If you use jQuery, you can use an AJAX call like this: $.ajax({ data: {lat : lat, lng: lng}, type: "POST", url: "script.php", success: function (e) { alert(e); }, error: function (er) { ...


1

Self-intersections are not as malicious as non-noded cross intersections. Here is some solutions 4 U: Automagical fixes by ST_MakeValid: update mytable set way = ST_Makevalid(way) where st_isvalid(way)=false And similar, but using st_buffer: update mytable set way = ST_Buffer(way,0) where st_isvalid(way)=false If it wont help this will give you ...


0

You can use ORDER BY, and LIMIT your result to 1 to find the closest road: SELECT gid, ST_Distance(ST_Transform(ST_GeomFromText('POINT(39.240443 26.208803)',3857),4326),geom) AS myLineDistance FROM road ORDER BY ST_Distance(ST_Transform(ST_GeomFromText('POINT(39.240443 26.208803)',3857),4326),geom) LIMIT 1 For the second query, you can convert the ...


0

Just query the table: SELECT name FROM planet_osm_polygon WHERE admin_level='4' AND ST_Contains(way, ST_GeomFromText('POINT(-11817883.86 4424094.21)',900913));


0

Ok. I finally got the drawing of the lines direction arrows by a little bit of trigonometry in the PostgreSQL(PostGIS) Query that generates the geometries for the ogr2ogr command to export it to the DXF file. The arrow triangle I get is not perfect, it has the tail a little bit moved but it will be enough for my needs. First I get all the points of the ...


1

With PostGIS 2.x, geometry columns typically use typmods like geometry(Point, 1234). For these you can use ALTER TABLE to directly modify the geometry column type in one step. For example, to set the SRID of geom in mytable to WGS84, use ST_SetSRID: ALTER TABLE mytable ALTER COLUMN geom TYPE geometry(Point, 4326) USING ST_SetSRID(geom, 4326); Note that ...


1

In Ubuntu 14.04 you also need to install the postgresql-9.3-postgis-scripts package. After I ran sudo apt-get install postgis postgresql-9.3-postgis-scripts I was then able to successfully run CREATE EXTENSION postgis; in my database to initialise PostGIS.


2

Query for ST_IsValid([your geometry]) = 'f'



Top 50 recent answers are included