Unable to resolve type:System.Data.Spatial.DbGeography with sql server geography data type returned in odata

Question

I have a table in sql server with geography type. I use entity framework and EntitySetController to return a [Queryable] list of the entity containing the geography column.

Here is an example of the returned data:

{
        "d":{
            "__count":"19534",
            "results":[
               {
                   "__metadata":{
                       "id":"http://localhost:2663/odata/Airports(1)",
                       "uri":"http://localhost:2663/odata/Airports(1)",
                       "type":"AirportDB.Airport"
                   },
                   "Id":1,
                   "SiteNumber":"50009.*A",
                   "Abbrev":"ADK",
                   "Name":"Adak",
                   "Type":"AIRPORT",
                   "GeoLocation":{
                       "__metadata":{
                           "type":"System.Data.Spatial.DbGeography"
                       },
                       "WellKnownValue":{
                           "__metadata":{
                               "type":"System.Data.Spatial.DbGeographyWellKnownValue"
                           },
                           "CoordinateSystemId":4326,
                           "WellKnownText":"POINT (-176.646 51.878)",
                           "WellKnownBinary":null
                       }
                   },
                   "LocationID":"'ADK",
                   "EffectiveDate":"\/Date(1367452800000)\/",
                   "Region":"AAL",
                   "DistrictOffice":"NONE",
                   "State":"AK",
                   "StateName":"ALASKA",
                   "County":"ALEUTIAN ISLANDS",
                   "CountyState":"AK",
                   "City":"ADAK ISLAND",
                   "FacilityName":"ADAK",
                   "Ownership":"PU",
                   "Use":"PU",
                   "Owner":"STATE OF ALASKA-DOTPF-CENTRAL RGN",
                   "OwnerAddress":"P O BOX 196900",
                   "OwnerCSZ":"\"ANCHORAGE, AK 99519\"",
                   "OwnerPhone":"    907-269-0751",
                   "Manager":"VINCE TUTIAKOFF",
                   "ManagerAddress":"P O BOX 1952",
                   "ManagerCSZ":"\"ADAK, AK 99546\"",
                   "ManagerPhone":"    907-592-8026",
                   "Latitude":51.877963888888893,
                   "Longitude":-176.64603055555554,
                   "ARPLatitude":"51-52-40.6700N",
                   "ARPLatitudeS":"186760.6700N",
                   "ARPLongitude":"176-38-45.7100W",
                   "ARPLongitudeS":"635925.7100W",
                   "ARPMethod":"E",
                   "ARPElevation":18,
                   "ARPElevationMethod":"S",
                   "MagneticVariation":"07E",
                   "MagneticVariationYear":2000,
                   "TrafficPatternAltitude":0,
                   "ChartName":"W ALEUTIAN ISLS",
                   "DistanceFromCBD":0,
                   "DirectionFromCBD":"W",
                   "LandAreaCoveredByAirport":0,
                   "BoundaryARTCCID":"ZAN",
                   "BoundaryARTCCComputerID":"ZAN",
                   "BoundaryARTCCName":"ANCHORAGE",
                   "ResponsibleARTCCID":"ZAP",
                   "ResponsibleARTCCComputerID":"ZAP",
                   "ResponsibleARTCCName":"ANCHORAGE OCEANIC",
                   "TieInFSS":"N",
                   "TieInFSSID":"CDB",
                   "TieInFSSName":"COLD BAY",
                   "AirportToFSSPhoneNumber":"907-532-2466",
                   "TieInFSSTollFreeNumber":"1-800-478-7250",
                   "AlternateFSSID":"ENA",
                   "AlternateFSSName":"KENAI",
                   "AlternateFSSTollFreeNumber":"1-866-864-1737",
                   "NOTAMFacilityID":"ADK",
                   "NOTAMService":"Y",
                   "ActiviationDate":"\/Date(-654912000000)\/",
                   "AirportStatusCode":"O",
                   "CertificationTypeDate":"I A S 04/2005",
                   "FederalAgreements":"N",
                   "AirspaceDetermination":"NO OBJECTION",
                   "CustomsAirportOfEntry":"N",
                   "CustomsLandingRights":"N",
                   "MilitaryJointUse":"N",
                   "MilitaryLandingRights":"Y",
                   "InspectionMethod":"F",
                   "InspectionGroup":"F",
                   "LastInspectionDate":9202012,
                   "LastOwnerInformationDate":1081991,
                   "FuelTypes":"A1",
                   "AirframeRepair":"NONE",
                   "PowerPlantRepair":"NONE",
                   "BottledOxygenType":"NONE",
                   "BulkOxygenType":"NONE",
                   "LightingSchedule":"SEE RMK",
                   "BeaconSchedule":"SS-SR",
                   "ATCT":"N",
                   "UNICOMFrequencies":0,
                   "CTAFFrequency":122.9,
                   "SegmentedCircle":"Y",
                   "BeaconColor":"CG",
                   "NonCommercialLandingFee":"",
                   "MedicalUse":"",
                   "SingleEngineGA":0,
                   "MultiEngineGA":0,
                   "JetEngineGA":0,
                   "HelicoptersGA":0,
                   "GlidersOperational":0,
                   "MilitaryOperational":0,
                   "Ultralights":0,
                   "OperationsCommercial":212,
                   "OperationsCommuter":0,
                   "OperationsAirTaxi":0,
                   "OperationsGALocal":0,
                   "OperationsGAItin":100,
                   "OperationsMilitary":28,
                   "OperationsDate":"\/Date(1325376000000)\/",
                   "AirportPositionSource":"",
                   "AirportPositionSourceDate":"",
                   "AirportElevationSource":"",
                   "AirportElevationSourceDate":"",
                   "ContractFuelAvailable":"",
                   "TransientStorage":"HGR",
                   "OtherServices":"CARGO",
                   "WindIndicator":"Y"
               }
            ]
        }
}

Jaydata, in factory config mode, throws an exception: "Unable to resolve type:System.Data.Spatial.DbGeography".

What am I doing wrong? How do I return the geography columns back to jaydata in a way it likes?

Thanks

EDIT:

@robesz Based on your JayStorm Paas Open Edition suggestion I created an account, created a matching schema, created a .Net console app to add some data, but it complains about an invalid format in the GeoLocation column. How do I fix that? Additionally, it first complained about needing odata v3 when I executed line SaveChanges(), but the import process hardcodes V2, so I hand edited the generated client side stub (not ideal) - how would I fix that? And also, is there no easier way to import data in to JayStorm tables? And finally, where can I find pricing about more than 1 million records?

 class Program
    {
        static void Main(string[] args)
        {
            var db = new AirportDB.mydatabaseService(new Uri("https://open.jaystack.net/c72e6c4b-27ba-49bb-9321-e167ed03d00b/6494690e-1d5f-418d-adca-0ac515b7b742/api/mydatabase/"));

            var airport = new Airport.Airport();
            airport.Abbrev = "Foo";
            airport.Name = "Bar";

            airport.GeoLocation = GeographyPoint.Create(51.8779638888889, -176.646030555556);
            db.AddToAirport(airport);

            db.SaveChanges();

        }
    }

score 0 · Answer 1 · answered Jun 14 '13 at 08:16

0

For Geo types we use GeoJSON. You can do the following: Let's suppose you have an EntitySet with the following structure:

 point:
    id: int,
    point: $data.GeographyPoint

when you retrieve your points use map (projection):

 context.points.map(function(p) { return { id: id, point: { lat: Latitude, lng: Longitude } }, {}, "default")

please let us know if it works for you or not

answered Jun 14 '13 at 08:16

Gabor Dolla

2,680
4
14
13

My issue is that using the add service wizard and using c# is the richest and easiest way for me to insert/update/delete data in mass quantities from either files or sql server tables. However once I include a GeoLocation type in my table the "add service" generated c# can no longer do anything - even selects - because of the conflict of the c# GeoLocation type with the server side type. Any help? – t316 Jun 17 '13 at 15:30

score 0 · Answer 2 · answered Jun 14 '13 at 08:23

Your service endpoint must expose the Geo points in OData standard format - Example responses can be found by inspecting JayData Geo examples.

You have a couple of options to do this: 1. ASP.NET WebAPI with WebAPI OData extension

Model:

public class Place
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Description { get; set; }
  public System.Spatial.GeographyPoint Location { get; set; }
}

Controller:

[Queryable(AllowedQueryOptions=AllowedQueryOptions.All)]
public IQueryable<Place> Get()
{
  data = new List<Place>();
  data.Add(new Place() { Id = 0, Name = "Place1", Description = "Desc", Location = GeographyPoint.Create(-1.56, 29.76) });
  data.Add(new Place() { Id = 1, Name = "Place1", Description = "Desc", Location = GeographyPoint.Create(-2.56, 19.76) });

  return data.AsQueryable();
}

As far as I know Entity Framework has some troubles with this type, but probably this has improved.

You can use the odata-server npm module on nodejs, this is the server-side JayData with useful helpers. You can expose the points, geo.distance() and geo.intersects() will work only with JayData MongoDB Provider Pro.
You can save your point to JayStorm PaaS Open Edition for free and build your own schema by point-and-clicks - adding a Geography Point field.

Can you pleae help me understand why column filters are not working? http://jsfiddle.net/t316/YZpcZ/5/ And also is there a way to have auto-increment long int primary keys instead of the built in object type? — t316, Jun 14 '13 at 22:22
And to my surprise the MVVM version seems work exactly the same way: http://jsfiddle.net/t316/4n62B/7/ Meaning it works nicely (but has the filter bug)... So, using the MVVM approach in durandal is probably running in to some weird promise issues... — t316, Jun 14 '13 at 22:45
it's a bug in jaystorm, we have the fix, we'll update jaystorm within days — Gabor Dolla, Jun 17 '13 at 16:24

Unable to resolve type:System.Data.Spatial.DbGeography with sql server geography data type returned in odata

2 Answers2