Tag Info

Hot answers tagged

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


6

Ironically, the fastest way to find the set of things not within other things is to do a full join that finds the contained things, but using a LEFT JOIN, so the un-matched things are hanging about to be found, thus: SELECT pts.* FROM pts LEFT JOIN polys ON ST_Contains(polys.geom, pts.geom) WHERE polys.id IS NULL; The un-matched rows in a left join are ...


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

In short: use ST_Dump, which will break your MULTIPOINT into its constituent parts. Then sort descending by ST_Y(geom) and get the first row. Here's a query that works on my PostGIS: SELECT ST_AsText(geom) FROM ST_Dump(ST_GeomFromText('MULTIPOINT(2 5, 3 1, 4 0)')) ORDER BY ST_Y(geom) DESC LIMIT 1


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


2

As far as I know, a table cannot have two geometry types (unless the type is a collection). Instead of modifying your existant table, try creating a new one: The geometry column is like any other column, it has one value for each row of the table. You are trying to create one geometry from many geometries, so what you want is not update the geometry of your ...


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": [ { ...


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


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

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

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

Measurement functions on geometry types do basic calculations in Cartesian space, where the output units are the same as the input distance units. This is well understood and documented (see ST_Length). WGS84 uses degrees, so ST_Length and ST_Area have outputs in degrees and degreesĀ². However for most practical purposes, these units do not make sense. You ...


1

The ST_Transform function has quite simple parameters: a geometry object and a target SRID. This should do it. SELECT name, ST_AsText(ST_Transform(point,2811)) FROM points;


1

Try using ST_3DIntersection instead.


1

How about SELECT EXISTS? According to the docs it should run until the first (if any) row is returned.


1

It's a bit difficult to determine exactly what you are trying to achieve. I've provided an example of how to handle the two scenarios that I think you are most likely trying to achieve. I have tried to keep the example short and simple, so I haven't included any SRID stuff and have just used INTs for coordinates. Scenario 1 is adding a line for each of ...


1

At some point you'll have to convert from Google native overlays to WKT. You can either do it in your frontend, using for example Wicket.js or in your backend, using postgis's ST_FromGeoJson. Keep in mind that there's no geoJSON spec for rectangles. They're just polygons. If you use Wicket.js you will be able to ingest a WKT BOX to generate a ...


1

Actually, you don't need to add a z-dimension in order to achieve the building-height that they use in that blog post. In fact, the map pluto image you posted is using the same cartocss method. Basically, you just need a column in your dataset that includes some variable for building-height. In their case, they have three columns, z_small, z_large, and ...



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