rgeo / activerecord-postgis-adapter

ActiveRecord connection adapter for PostGIS, based on postgresql and rgeo
Other
870 stars 237 forks source link

RGeo::Geographic::SphericalPointImpl return nil #289

Open Tioneb12 opened 6 years ago

Tioneb12 commented 6 years ago

pry(main)> p = Poi.last

 Poi Load (0.3ms)  SELECT  "pois".* FROM "pois" ORDER BY "pois"."id" DESC LIMIT $1  [["LIMIT", 1]]
=> #<Poi:0x00007fed8f6aefa8
 id: 7,
 latitude: 44.56,
 longitude: 2.37,
 lonlat: #<RGeo::Geographic::SphericalPointImpl:0x3ff6c7b2a7e8 "POINT (2.3749903 44.56567829999999)">

"lonlat" is not empty but...

pry(main)> p.lonlat => nil

for some method, "lonlat" work fine... for other not, for exemple :

respond_to do |format|
  format.html
  format.json { render json: { type: 'FeatureCollection', features: Poi.pois_geojson(@pois) }}

return :

    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "type": "Point",
        "coordinates": null
      }
    }

Why ? What's wrong ?

Thanks for your help

Tioneb12 commented 5 years ago

In pgadmin I can get "lonlat" :

capture d ecran 2018-11-23 a 20 23 56

In my console or in rails I can't get "lonlat" :

capture d ecran 2018-11-23 a 20 23 20

I don't understand why

@poi = Poi.find(13) @poi.lonlat = nil

pedros007 commented 5 years ago

@Tioneb12 maybe this will help you find the issue:

In an IRB console, try something like this:

irb(main):007:0> parser = RGeo::WKRep::WKBParser.new
irb(main):008:0> parser.parse("0101000000959636C1DCE30740BFF1B567964F4640")
=> #<RGeo::Geos::CAPIPointImpl:0x2b14ab6c48b0 "POINT (2.9862609 44.6217775)">

You might also paste the actual WKB rather than an image of WKB. When I try with your WKB of 0101000020E6100000959636C1DCE30740BFF1B567964F4640, I get an error:

irb(main):009:0> parser = RGeo::WKRep::WKBParser.new
irb(main):010:0> parser.parse('0101000020E6100000959636C1DCE30740BFF1B567964F4640')
Traceback (most recent call last):
        1: from (irb):10
RGeo::Error::ParseError (Unknown type value: 536870913.)

What projection is the data in? Not sure what it means at the moment, but some of the first bytes of WKB are different from your vs my working example:

Source  First couple of characters of WKB The rest of the WKB
mine 01010000 00959636C1DCE30740BFF1B567964F4640
yours 0101000020E61000 00959636C1DCE30740BFF1B567964F4640

Wonder what the different characters 20E61000 represent... perhaps something that is making the parser fail with Unknown type value: 536870913? Note in older versions of RGeo, errors would return nil rather than raise an exception.

Tioneb12 commented 5 years ago

Thanks Pedros for your help.

I use Rgeo 1.2.2 with srid 4326

Initializer.rb :

RGeo::ActiveRecord::SpatialFactoryStore.instance.tap do |config| config.default = RGeo::Geographic.spherical_factory(srid: 4326) end

and I don't know what's "536870913" maybe my initializer is not good.

I found this answer on the web : https://groups.google.com/forum/?nomobile=true#!msg/rgeo-users/rgWJGPzX2RI/HK1gAgnO16AJ

What must I do ?

pedros007 commented 5 years ago

@Tioneb12 So does this work for you?

parser = RGeo::WKRep::WKBParser.new
parser.parse("0101000000959636C1DCE30740BFF1B567964F4640")

What is the output of RGeo::Geos.supported??

In config/database.yml, do you have adapter: postgis?

What happens if you delete your initializer, start a new console and try to puts p.lonlat again?

Open a psql console and describe the pois table (I think it would be \d pois). Specifically I'm looking to see what the geometry column looks like.

Tioneb12 commented 5 years ago
[14] pry(main)> parser = RGeo::WKRep::WKBParser.new
=> #<RGeo::WKRep::WKBParser:0x00007f8e8ecd0158
 @default_srid=nil,
 @exact_factory=nil,
 @factory_generator=#<Method: RGeo::Cartesian.preferred_factory>,
 @ignore_extra_bytes=false,
 @support_ewkb=false,
 @support_wkb12=false>
[15] pry(main)> parser.parse("0101000000959636C1DCE30740BFF1B567964F4640")
=> #<RGeo::Geos::CAPIPointImpl:0x3fc7471b8700 "POINT (2.9862609 44.6217775)">
[16] pry(main)> RGeo::Geos.supported?
=> true

In config/database.yml, I have adapter: postgis

If I delete my initializer I always have p.lonlat=nil

Table "public.pois"
    Column     |          Type          | Collation | Nullable |             Default              
---------------+------------------------+-----------+----------+----------------------------------
 id            | bigint                 |           | not null | nextval('pois_id_seq'::regclass)
 address       | character varying      |           |          | 
 address2      | character varying      |           |          | 
 zip_code      | integer                |           |          | 
 city          | character varying      |           |          | 
 active        | boolean                |           |          | false
 latitude      | double precision       |           |          | 
 longitude     | double precision       |           |          | 
 lonlat        | geography(Point,4326)  |           |          | 
 lonlatheight  | geography(PointZ,4326) |           |          | 
 poitable_type | character varying      |           |          | 
 poitable_id   | bigint                 |           |          | 
 user_id       | bigint                 |           |          | 
Indexes:
    "pois_pkey" PRIMARY KEY, btree (id)
    "index_pois_on_poitable_type_and_poitable_id" btree (poitable_type, poitable_id)
    "index_pois_on_user_id" btree (user_id)
Foreign-key constraints:
    "fk_rails_4fc3b89561" FOREIGN KEY (user_id) REFERENCES users(id)
Tioneb12 commented 5 years ago

This is strange because, for example, this method (and others) works well :

  def closet_poi_from
    closet_sql = <<-SQL
    SELECT
      ptb.* AS pois
      FROM pois pta, pois ptb
        WHERE pta.id = #{self.id}
        AND ST_DWithin(pta.lonlat::geometry, ptb.lonlat::geography,5000)
    SQL
    Poi.find_by_sql(closet_sql)
  end
pedros007 commented 5 years ago

Have you tried this initializer from the rgeo-activerecord README?

# config/initializers/rgeo.rb
RGeo::ActiveRecord::SpatialFactoryStore.instance.tap do |config|
  # By default, use the GEOS implementation for spatial columns.
  config.default = RGeo::Geos.factory_generator

  # But use a geographic implementation for point columns.
  config.register(RGeo::Geographic.spherical_factory(srid: 4326), geo_type: "point")
end
Tioneb12 commented 5 years ago

I just tried to do that, but always have p.lonlat =nil

Tioneb12 commented 5 years ago

I tried to do something about it :

[7] pry(main)> z
=> #<Poi:0x00007f8eeb7411d0
 id: 13,
 address: "Place des fêtes",
 address2: nil,
 zip_code: 12470,
 city: "Aubrac",
 active: true,
 latitude: 44.6217775,
 longitude: 2.9862609,
 lonlat: #<RGeo::Geos::CAPIPointImpl:0x3fc779110ed8 "POINT (2.9862609 44.6217775)">,
 lonlatheight: nil,
 poitable_type: "Road",
 poitable_id: 1,
 user_id: 1>
[8] pry(main)> z.update(lonlat: "POINT(2.9862609 44.6217775)")
   (1.2ms)  BEGIN
  User Load (44.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  SQL (1.3ms)  UPDATE "pois" SET "lonlat" = '0020000001000010e64007e3dcc136969540464f9667b5f1bf' WHERE "pois"."id" = $1  [["id", 13]]
   (1.7ms)  COMMIT
=> true
[9] pry(main)> z.lonlat
=> "POINT(2.9862609 44.6217775)"
[10] pry(main)> 

It works !!

Maybe, the problem would come from my method for set lonlat ?

  def set_st_point
    if latitude.present? && longitude.present?
      update_column('lonlat', make_point(longitude, latitude))
    end
  end
def make_point(longitude, latitude)
    point_sql = <<-SQL
    select
    ST_SetSRID(ST_MakePoint(#{longitude}, #{latitude}), 4326) as poi
    SQL
    Poi.find_by_sql(point_sql).first.poi
  end

But after update, if I go to my rails app, lonlat stay nill

capture d ecran 2018-11-28 a 13 40 47