1

How i can transform the geometry WKB for example:

010600002031BF0D000100000001030000000100000004000000CE001744EA0212414BB26942EFE95641DD9AF00E49FF1441706072C152EC56413F53AF6BF9191441242DF04F7EC85641CE001744EA0212414BB26942EFE95641

in the same geometry with SRID 900913?

Ok, i think i found the solution, i use the next:

SELECT ST_SetSRID(st_astext(st_transform('010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141'::geometry,4326)),900913)

and i seems to be working

4

If you run, using your original geometry:

SELECT  
    ST_AsText(
        ST_GeomFromEWKT('010600002031BF0D000100000001030000000100000004000000CE001744EA0212414BB26942EFE95641DD9AF00E49FF1441706072C152EC56413F53AF6BF9191441242DF04F7EC85641CE001744EA0212414BB26942EFE95641'
    ));

you will see:

MULTIPOLYGON(((295098.566494 6006717.0377012,344018.26458971 6009163.022606,329342.355161 5972473.2490342,295098.566494 6006717.0377012)))

or with you second EWKT string, you get:

MULTIPOLYGON(((-125344.50185865 4574113.3530299,-125275.23080178 4574135.4481084,-125242.38676619 4574016.612416,-125311.65782306 4573996.3088304,-125344.50185865 4574113.3530299)))

Both of these are already in 900913. If you look at the definition for EPSG:900913, which, note, is now called 3857, you will see your bounds are within those limits -- very large numbers, as meters covering the whole planet.

You second query seems wrong, as you are forcing this string to 4326, which it is not, and then converting it to 900913. In fact, if you wrap your query in ST_Astext, you will see that what you are getting back is not in 900913 at all.

SELECT 
    ST_AsText(
         ST_SetSRID(
              ST_AsText(
                  ST_Transform('010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141'::geometry,
         4326)),
    900913));

returns:

MULTIPOLYGON(((-1.12598881799973 37.9623844064892,-1.1253665455084 37.9625408936206,-1.12507150251677 37.9616992426904,-1.1256937750081 37.9615554420694,-1.12598881799973 37.9623844064892)))

which is almost certainly 4326, lat/lon.

As it is already in 3857 (900913), all you need to do is wrap it in ST_SetSRID, ie,

SELECT ST_SetSRID(ST_GeomFromEWKT(<your ewkt geom>, 900913));

which will make the geometry you are loading have the SRID 900913 and get rid of the error message:

Geometry SRID (0) does not match column SRID (900913)

  • Ok, i use ST_SetSRID(st_astext('0106........E8725141'),900913) and also work – josemi May 17 '16 at 14:01
  • Yes, exactly, the coordinates are in 900913, using ST_SetSRID makes the geometry 900913, which is what the column is expecting. – John Powell May 17 '16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.