postgresqlpostgisgeojsontilestache

Tilestache - Mismatch between the coordinates present in the PostgreSQL/PostGIS and those returned by PostGeoJSON provider


This is the relevant bit my Tilestache config,

"points-of-interest":
 {
  "provider":
  {
    "class": "TileStache.Goodies.Providers.PostGeoJSON.Provider",
    "kwargs":
    {
       "dsn": "dbname=database user=username host=localhost",
       "query": "SELECT loc_id AS __id__, loc_name, geo2 AS __geometry__ FROM  location",
       "id_column": "__id__", "geometry_column": "__geometry__"
    }
  }
 },

When I access - http://127.0.0.1:8080/points-of-interest/0/0/0.json

I get the Response -

{
  "type": "FeatureCollection",
  "features": [
  {
    "geometry": {
    "type": "Point",
    "coordinates": [
      -0.0008691850758236021,
      0.0002956334943026654
    ]
  },
  "type": "Feature",
  "properties": {
    "loc_name": "TI Blvd, TX"
  },
  "id": 9
  }
]}

The coordinates in the above response are -

"coordinates": [-0.0008691850758236021,0.0002956334943026654]  

Where as the actual coordinates in the db table are -

database=# SELECT loc_id AS __id__, loc_name, ST_AsText(geo2) AS __geometry__ FROM location;
 __id__ |  loc_name   |       __geometry__
 --------+-------------+---------------------------
  9 | TI Blvd, TX | POINT(-96.75724 32.90977)

What am I missing here? Why are my GeoJSON response coordinates different?

The table description is

 Table "public.location"
 Column  |          Type          | Modifiers
 ----------+------------------------+-----------
 loc_id   | integer                | not null
 loc_name | character varying(70)  |
 geo2     | geometry(Point,900913) |
 Indexes:
 "location_pkey" PRIMARY KEY, btree (loc_id)

Thank you all in advance for all the help.


Solution

  • Inserting the point with SRID - 4326 fixed the issue.

    This is the insert -

    INSERT INTO location(loc_id, loc_name, geo2) VALUES (3, 'Manchester, NH', ST_Transform(ST_GeomFromText('POINT(-71.46259 42.99019)',4326), 900913));