Skip to main content
I use SQL creator to import a view from SQL server. It represents lines but comes in as multipoint geometry.
The linebuilder to create lines from multipoints doesn't work. Any ideas instead of de-aggregating to individual points?

Have you tried a GeometryCoercer? Not quite sure what it will do in your case without a sample set of data.


Hi, I just tried it but has no effect. The input has geometry aggregate. I applied aggregate filter and a a geometry refiner and wrote to File Geodatabase as a multipoint. Although from there to lines I have no clue. A multipoint to line would seem logical in FME, but I couldn't find it?. thanks


Hi, I just tried it but has no effect. The input has geometry aggregate. I applied aggregate filter and a a geometry refiner and wrote to File Geodatabase as a multipoint. Although from there to lines I have no clue. A multipoint to line would seem logical in FME, but I couldn't find it?. thanks

Try using the Microsoft SQL Server Spatial reader. That should pull in the geometry properly.


Hi @lennertpronk​ , Deaggregator and LineBuilder would be a solution, but if you don't want to do that, a PythonCaller with this script might help you. Assuming that input geometry is always a MultiPoint.

import fmeobjects
def replaceMultiPointWithLine(feature):
    coords = Wp.getXYZ() for p in feature.getGeometry()]
    feature.setGeometry(fmeobjects.FMELine(coords))

 


Hi @Takashi Iijima​ 

 

Thanks, Although get this error

 

FFS writer: Writing 1 feature(s) to file 'C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.fts'PythonCaller (PythonFactory): Splitting bulk mode features into features

PythonFactory failed to load python symbol `FeatureProcessor'

Factory proxy not initialized

PythonCaller (PythonFactory): PythonFactory failed to process feature

A fatal error has occurred. Check the logfile above for details

Microsoft SQL Server Spatial Reader: Closing `AFRW_WHX_REG_WGS84' . Read operation complete

Stored 1 feature(s) to FME feature store file `C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.ffsupdating'

Saving spatial index into file 'C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.fsi'

Finished saving spatial index into file 'C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.fsi'

A fatal error has occurred. Check the logfile above for details

Translation FAILED with 5 error(s) and 0 warning(s) (0 feature(s) output)

FME Session Duration: 2.3 seconds. (CPU: 0.5s user, 0.6s system)

END - ProcessID: 20620, peak process memory usage: 166184 kB, current process memory usage: 125980 kB

A fatal error has occurred. Check the logfile above for details

Program Terminating

Translation FAILED.

 

 


What is your SQL in the SQL Creator?

 

I agree with @jlbaker2779​ that the Microsoft SQL Server Spatial reader should do the trick here. You mention that you are creating a view though so that could add a bit of a challenge. There is a parameter called SQL to run before read which might help


select * from dbo.[DovSeismicLine2DGeometry]

 


A little sample straight out of sql server spatial


A little sample straight out of sql server spatial

ok so in this case your points are ordered so you can just use a deaggregator to get the individual points and then use a LineBuilder where you group by Line_ That gave me 9 nice looking lines


@virtualcitymatt​ 

HI thanks. Although i have 100 Thousand lines and 20 Million points. There much be a quicker why to do it.

Essentially my multi-points are already lines , sorted etc... So wonder if there is some geometry replacer to have it run more economically instead of deaggregating and aggregating again.


Hi @Takashi Iijima​ 

 

Thanks, Although get this error

 

FFS writer: Writing 1 feature(s) to file 'C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.fts'PythonCaller (PythonFactory): Splitting bulk mode features into features

PythonFactory failed to load python symbol `FeatureProcessor'

Factory proxy not initialized

PythonCaller (PythonFactory): PythonFactory failed to process feature

A fatal error has occurred. Check the logfile above for details

Microsoft SQL Server Spatial Reader: Closing `AFRW_WHX_REG_WGS84' . Read operation complete

Stored 1 feature(s) to FME feature store file `C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.ffsupdating'

Saving spatial index into file 'C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.fsi'

Finished saving spatial index into file 'C:\\Users\\LOCAL_~1\\Temp\\wb-cache-FME_OSP_UA-kvoQQG\\Main_SQLCreator_11 -1 98 fo 0 RESULT 0 e0a5d9da31d17caaa4198afb8bc1d1604953e978.fsi'

A fatal error has occurred. Check the logfile above for details

Translation FAILED with 5 error(s) and 0 warning(s) (0 feature(s) output)

FME Session Duration: 2.3 seconds. (CPU: 0.5s user, 0.6s system)

END - ProcessID: 20620, peak process memory usage: 166184 kB, current process memory usage: 125980 kB

A fatal error has occurred. Check the logfile above for details

Program Terminating

Translation FAILED.

 

 

You have to set the function name "replaceMultiPointWithLine" to the Class or Function to Process Features parameter in the PythonCaller.


Thanks that solved it. I had to de-aggregate after all as get(XYZ) only works on Points 😀

 


Reply