Hi Steve,
Does this help?
select * from
(select gid,item,data, ROW_NUMBER() OVER (Partition by item order by gid)
as subid from a) as t1
join
(select gid,item,moredata, ROW_NUMBER() OVER (Partition by item order by
gid) as subid from b) as t2
on t1.item=t2.item and t1.subid=t2.subid;
Cheers
So I've was running this query for 866000 s (10 days) before I decided to kill
it:
create table taz_and_lancover_10_fast_2 as
SELECT p.lc_class, n.taz
, CASE
WHEN ST_CoveredBy(p.geom, n.the_geom)
THEN p.geom
ELSE
ST_Multi(
ST_Intersection(p.geom,n.geom)
) END AS
Hey,
you could try to not use CASE (so separate the spatial join from the
processing, which is easy to parallelize (assuming you have more than one
core )).
First generate the table with
CREATE TABLE psatial_mapping_between_lancover_and_taz AS
SELECT row_number() over() as row_id,
Hi,
I am using Postgres 9.3 and have a table with geometry columns:
startloc geometry(Point),
endloc geometry(Point),
When I run a query that crosses the date line boundary, I'm getting incorrect
results. Example:
SELECT * from od1.trip_v1_partitioned where startts=TIMESTAMP
Hi Trang,
Geometry types work in Cartesian space, and don't know what happens
when Y coordinates cross the antimeridian at -180/+180. Try
experimenting with the geography type, which works with some, but not
all PostGIS functions. ST_Intersects should work on geography types
for your example,
*I will write up a tutorial explaining the benefits of vector tiling in
PostGIS, with examples and parallelisation code patterns. If not today,
hopefully over the next week. *
Would be nice if you have the time!
Best,
Andre
On Fri, Feb 20, 2015 at 2:52 AM, Mark Wynter
Hi,
There are two ways to draw a line between (-179, 0) (179 0) - the long
short way around the earth, Postgis can't tell which is correct.
You could:1. cast the geometry to a geography for the query,2. try
ST_ShiftLongitude([geometry]) which will change it to a 0-360 longitude space
So I've was running this query for 866000 s (10 days) before I decided to
kill it:
One potential thing I've realized is that a few of the geometries in tazjan2
are multipolygons, not single polygons. But it's only a few. There are a
few very large and complex polygons in
Thanks for the hint, Rémi. I'll give it a try, to see if the st_intersection
is slow (as compared to just st_intersects).
Here's my EXPLAIN ANALYZE for my 5 zone test:
Nested Loop (cost=1.56..80.91 rows=8 width=560) (actual
time=3929258.738..19756123.479 rows=29 loops=1)
Join Filter:
