Postgis add Geography to function()?

Question

i was wondering

im getting different results when using geography() on different positions within a query.

So, whats the most accurate way of forcing geography() over a functions with geometry columns?

example:

---

SELECT

polys.id,

sum(st_length(st_intersection(geography(lines.the_geom),geography polys.the_geom))))/1000 total_lengths

from schema.table1polys polys schema.table2lines lines on ST_Intersects(geography(lines.the_geom),geography(polys.the_geom))

---

..or..

---

SELECT

polys.id,

sum(st_length(geography(st_intersection(lines.the_geom,polys.the_geom))))/1000 total_lengths

from schema.table1polys polys schema.table2lines lines on ST_Intersects(geography(lines.the_geom),geography(polys.the_geom))

whats the most accurate? i have sort of 2 / 3 Km of difference between both querys.

Answer 1

You should cast all geometry DB fields to geography and then do the calculations:

SELECT
polys.id,
sum(st_length(st_intersection(geography(lines.the_geom),geography(polys.the_geom))))/1000      total_lengths
from schema.table1polys polys schema.table2lines lines 
on ST_Intersects(geography(lines.the_geom),geography(polys.the_geom));